countif with 3 arguments?

[JENNYC]

Hi Excel Experts!
I am going out of my mind trying to figure this out!! Please help!
Basically I need to add a 3rd argument to the "countif" function. I know, i know...can't do that!! Which is why I'm asking y'all to lend me hand! Let me try and lay out the scenario for you: this worksheet is calculating "special" commissions, if that helps you any!

I need Excel to look down column A and recognize a specific salesperson's name (it will be in the list a number of times). Then i need it to look across to the amounts in columns C through G and tell me the count...not the sum...of numbers in column C, D, etc. everytime it sees that specific name. If Excel allowed a 3rd argument for the "countif" function i think it would look like this:
=countif(a3:a100,"jim",c3:c100) ...I have already used "sumif" to give me the totals...but i need to know HOW MANY were counted for each of those sums!! Is this possible!? Please help! Please help!

Thanks in advance!!
Jenny

[hirenk]

I have a similar problem which I cant seem to find a solution to - in column A I have a list of sales people, and I use the spreadsheet to count the number of add ons they have to their sales.

Column A lists their names, Column B and Column C has either "yes" or "no" to indicate whether or not they added Product 1 or Product 2 to their sale.

What I am looking for is a formula to count how many Product 1's and how many Product 2's each of the sales people sold.

i.e. in the following case:

Names Product 1 Product 2
Jenny yes yes
Paul yes no
Jenny no yes
Paul yes no
Tom yes yes
Jenny no no

the results I would want to see are:

Names Product 1 Product 2
Jenny 1 2
Paul 2 0
Tom 1 1

Is this even possible? Any help would be appreciated!!

[JENNYC]

That is EXACTLY what i am trying to do as well! Please help us if you can!!

[duane]

firstly, why not just count the ocurances of "Jim" in column A?

Otherwise, see this link - it should help

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

or else

=count(if(a3:a100="Jim",c3:c100))

entered with control+shift+enter

[JENNYC]

Well thank you soooo very much for the help Duane, but I couldn't get that forumula to work either!?

...and i can't just the occurances of "Jim" in column A because I need to know how many products jim sold from column C, how many in D, etc...

Do you....or anyone else!!... have any more advice for me??

[RES]

Duane gave you all a good pointer with the sumproduct link

It is a bit tricky to get yer head round but is the way

assume the first example from hirenk is located from A1 on

Names Product 1 Product 2
Jenny yes yes
Paul yes no
Jenny no yes
Paul yes no
Tom yes yes
Jenny no no

then to count instances of Jenny and Product 1

{=SUMPRODUCT(--(A2:A6="Jenny"),--(B2:B6="yes"))}
Note: it is an array formulae so Ctrl, Shift Enter

If you wanted to count all double yes sales for Paul

{=SUMPRODUCT(--(A2:A6="Paul"),--(B2:B6="yes"),--(C2:C6="yes"))}
Note: it is an array formulae so Ctrl, Shift Enter

For more detail on how it works follow the link shown in the earlier post

good luck
RES