countif with 3 arguments?

**JENNYC** · 02-07-2005, 08:23 PM

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** · 02-07-2005, 10:05 PM

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** · 02-07-2005, 10:15 PM

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

**duane** · 02-07-2005, 10:37 PM

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** · 02-08-2005, 11:26 AM

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** · 02-08-2005, 11:47 AM

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

**MattShoreson** · 02-08-2005, 11:57 AM

In the last correct example -

=SUMPRODUCT(--(A2:A6="Jenny"),--(B2:B6="yes"))

If doenst have to be an array formula.

**RES** · 02-08-2005, 12:10 PM

Matt good spot and a surprise to me

Cheers RES

**JENNYC** · 02-08-2005, 01:56 PM

Ok, please be patient with me!! I feel like an idiot because i am still struglling with this!! The information you all have provided is incredibly helpful though!!...as is that link! I just can't get any of the suggested formulas to work for this specific scenario...maybe i am doing something wrong!?...let me explain again quick:

Column A Column B column c column d....
Jim 205 115
Jenny 530 200
Jim 330 200
Jim 530 115
Paul 350 330
Jenny 200
Paul 530 400

What formula should I use to use to find Jim's name and then tell me the number of times there is any number at all in column b, c, d...(instead of recognizing a specific word or number)

The answers it should give me are:
Column B
Jim: 2
Jenny: 1
Paul: 2

Column C
Jim: 3
Jenny: 0
Paul: 1
etc...

Sorry if i'm making it hard to understand! Thank you all again for your help!!!

**JENNYC** · 02-08-2005, 04:07 PM

I am sorry! The last response i entered made no sense! My table didn't turn out as I'd hoped! Let me make it a shorter question:

this is the formula recommended to us:
=SUMPRODUCT(--(A2:A6="JENNY"),--(B2:B6="YES"))

Is there a specific command I can use in place of "yes" to make Excel recognize & count just any entry in column b? My entries in column B,C, D... will be a variety of different numbers & I need to know how many entries Jenny has in column B, C, D...etc.....