SOUTHAMERICA70
asked on
Count based on criteria in Excel
Hello
In Excel 2003. I wanted to count based on a certain criteria.
For example..I wanted to get the total count of all QP's* in column B based on a certain value in Column C , 00075. The result should be a total count of 2.
Column A Column B Column C
Saurabh QP 00076
Amit BAR 00075
Piyush QP 00075
Pradeep BAR 00076
Jeeb QP PET 00075
In Excel 2003. I wanted to count based on a certain criteria.
For example..I wanted to get the total count of all QP's* in column B based on a certain value in Column C , 00075. The result should be a total count of 2.
Column A Column B Column C
Saurabh QP 00076
Amit BAR 00075
Piyush QP 00075
Pradeep BAR 00076
Jeeb QP PET 00075
=CountIf(B1:B50,"QP*")+Cou ntIf(C1:C5 0,"00075")
ASKER
kaapsliic: Thanks but I could not get the formula to count at all.
=SUMPRODUCT((B1:B100="QP") *(C1:C100= "00075"))
should do it.
should do it.
COUNTIFS will only work in 2007 or later - in 2003 or earlier you need SUMPRODUCT.
ASKER
hitsdoshi1: The count total I got was 6. It should be 2. Wanted to get the total counts of all QP* against 00075.
PS Or a SUM(IF(...)) array formula but SUMPRODUCT is more efficient.
Jap, that's in MS Office 2010 - MS 2003 has only COUNTIF, then try some of other commented formulas.
ASKER
rorya: I got 0 for my count
ASKER
Here is an attached excel sheet.... Count.xls
This should work...
=COUNTIFS(B1:B50;"QP*";C1: C50;"00075 ")
=COUNTIFS(B1:B50;"QP*";C1:
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hitsdoshi1: Negative...I got an error
ASKER
Thanks Rorya! Works like a charm!
=COUNTIFS(B1:B5;"QP*";C1:C
Firs it searches for all what contains "QS" in Column B and then from Column C all what contains 75