help with counting text in multiple columns?

[krasp]

Hello!! I am a newbie so I have to get out the usual thank the lord for the board and its users, etc. So...

I have text in clumns A and B but only want to count certain entries in A based on the entry in B.. Detailed question after the html code...

Thanks in advance!! :D

Book1.xls
	A	B	C	D	E	F
1	AGENT	ORDERSOURCE	COPLETED	SOURCE	TOTAL	DAVE
2	MIKE	DIAMOND	YES	DIAMOND	3	#VALUE!
3	DAVE	DIAMOND	YES	CLAY	2
4	JIM	MILLER	NO	MILLER	4
5	MIKE	CLAY	YES	TOTALS	9
6	MIKE	DIAMOND	YES
7	JIM	MILLER	NO	COMPLETED	6
8	JIM	MILLER	YES	NOTCOMPLETE	3
9	JIM	CLAY	NO
10	APRIL	MILLER	YES
needtextcountingformulas

I need a formula to count how many times DAVE received an order from DIAMOND.
I assumed something like =COUNT(B:B,"DIAMOND")*IF(A:A,DAVE) but that doesn’t work
ALSO
I need a formula to count how many times DAVE received an order from DIAMOND that were 'completed'.

 

[pgc01]

Hi krasp
Welcome to the board

For the first question try

=SUMPRODUCT(--(A2:A10="DAVE"),--(B2:B10="DIAMOND"))

For the second:

=SUMPRODUCT(--(A2:A10="DAVE"),--(B2:B10="DIAMOND"),--(C2:C10="YES"))

Hope this helps
PGC

EDIT: Replaced DIAMOND by "DIAMOND"

 

[krasp]

...and yes, this simple task is aching my brain! lol :D

 

[pgc01]

Hi again

Please notice that I edited the formulae and replaced DIAMOND by "DIAMOND"

 

[krasp]

PGC, Thanks for the quick reply! I have tried the SUMPRODUCT formula as well as the COUNT*IF but I keep getting a #NAME? error with all my attempts.

 

[krasp]

PGC, Yes I changed the DIAMOND to "DIAMOND" and it return a 0. instead of a 1.

 

[Aladin Akyurek]

Easier if related to cells you have...

=SUMPRODUCT(--($A$2:$A$10=F$1),--($B$2:$B$10=$D2))

 

[krasp]

Aladin,
This formula works, Can you exlain to me how it works? Or, how to replicate it? THANK YOU!!!

Chris

 

[pgc01]

Hi again Krasp

Aladin's formulation is, no doubt, better.

I was just trying to show you the mechanics of counting with multiple criteria.

I just tried and the formula worked fine for me. Could it be that you have, for ex., spaces after the names?

Kind regards
PGC

 

[krasp]

pgc, I don't have sapces after the entries. Thank you for showing me an alternative counting method!! :D

Aladin, Can you use that same formula to also use coulmn C as criteria (only count A:A if MIKE, if B:B is DIAMOND and C:C is YES