Microsoft Excel Functions

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

Simon

Hi,

Right this is my problem, I have a master spreadsheet in
which I want to count how many queries are coming in for a
specified person.

So for example Joe A and Joe B are on the phones, their
names are in Cells A1:A10 another spreadsheet called for
example "Incoming Calls". Everytime one of them recieves a
call they put their name in the "A" cells, e.g A1=JoeA,
A2=JoeB,A3=JoeB etc. So it can be totally random but every
time they put their name down they also need to put down
the query they recieved. E.g A2="JoeB", B2="Complaint".

I would like to use the name as the key so on the master
spreadsheet I know how many "Complaints" Joe A or B have
dealt with or any other queries.

I think its some sort of COUNTIF function but I can not
get it to work.

Any help would be much appreciated.

Many Thanks in advance.

Simon
 
Simon, try this =SUMPRODUCT((A2:A10="Joe B")*(B2:B10="Complaint"))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
Thanks Paul,

I'll give that a whirl.

Simon
-----Original Message-----
Simon, try this =SUMPRODUCT((A2:A10="Joe B")* (B2:B10="Complaint"))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **



.
Click to expand...
 
Unfortunately that didn't work as it didn't count and only
returned a value of 0.

Any other suggestions guys??

Simon
 
Hi,

Paul unfortunately that formula did not work??

Help please?

Or any other ideas guys??

Many Thanks

Simon
 
Your illustration referred to "JoeB"; the formula referred to "Joe B".
Is that the problem?

Alan Beban
 
Simon, as Alan pointed out "Your illustration referred to "JoeB"; the
formula referred to "Joe B". " where my formula has a space between Joe and
B, if that is the case just delete the space and it should work if the
formula is one the same sheet, I missed the part about "another spreadsheet"
so if you wnat the formula on another sheet try this

=SUMPRODUCT(('Incoming Calls'!A2:A10="Joe B")*('Incoming
Calls'!B2:B10="Complaint"))

if its in another workbook you will also have to refrence the workbook like
this
=SUMPRODUCT(('[Book2]Incoming Calls'!A2:A10="Joe B")*('[Book2]Incoming
Calls'!B2:B10="Complaint"))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
Back
Top