Excel formula's

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

Right its a worksheet in Excel 2003.
Caller Query
Joe A Complaints
Joe B Complaints
Joe A Complaints
Joe A Shipping
Joe A Refund
Joe B Complaints
Joe A Complaints

Above is what my worksheet looks like. In another
worksheet I need a formula for a cell to count how
complaints/shipping queries each caller takes. So for
example in my results sheet it should show:

Shipping Complaints
Joe A 1 3

I need a formula to do the above??

Any help would be very much appreciated.

Many Thanks in advance.

Simon
Click to expand...
 
I shall assume that you have your list in Sheet1, starting at A1, and that
you want the results in another sheet where "Shipping" is in B1 and "Joe A"
is in A2. Put this formula in B2:
=SUMPRODUCT((Sheet1!$A$1:$A$20=$A2)*(Sheet1!$B$1:$B$20=B$1))
Then copy this cell across to C2, D2... (as far as you have different
queries).
Then copy this line of formulas down to row 3, row4. .. (as far as you have
different callers).
 
Thanks for the reply Paul,

That didn't seem to work as the value returned is "10"

I'm not sure but I think its counting the number cells in
the range not the number of actual callers or queries.

Have I done it correctly?? i just copied and pasted.

Many Thanks

Simon
 
Are the ranges correct for your data?

You can troubleshoot a formula by selecting part of it in the formula bar
and pressing F9, when you will see the result of that part of the formula.
Consider
=SUMPRODUCT((Sheet1!$A$1:$A$20=$A2)*(Sheet1!$B$1:$B$20=B$1))
Select, for example,
Sheet1!$A$1:$A$20=$A2
and press F9. You will see a list of TRUE and FALSE values. Does this
correspond with what you expect, looking at your data (i.e. TRUE where you
have "Joe A")?
 
Back
Top