# 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
Jeeb                        QP  PET         00075

###### Who is Participating?

Commented:
Two issues - you have trailing spaces in column B and your C values are actually numbers formatted to display leading zeroes, so you want:

=SUMPRODUCT((TRIM(B1:B100)="QP")*(C1:C100=75))
0

Commented:
For you I suggest using COUNTIFS function and the formula would be:
=COUNTIFS(B1:B5;"QP*";C1:C5;75)

Firs it searches for all what contains "QS" in Column B and then from Column C all what contains 75
0

Commented:
=CountIf(B1:B50,"QP*")+CountIf(C1:C50,"00075")

0

Author Commented:
kaapsliic:  Thanks but I could not get the formula to count at all.
0

Commented:
=SUMPRODUCT((B1:B100="QP")*(C1:C100="00075"))
should do it.
0

Commented:
COUNTIFS will only work in 2007 or later - in 2003 or earlier you need SUMPRODUCT.
0

Author Commented:
hitsdoshi1: The count total I got was 6.  It should be 2.  Wanted to get the total counts of all QP* against 00075.
0

Commented:
PS Or a SUM(IF(...)) array formula but SUMPRODUCT is more efficient.
0

Commented:
Jap, that's in MS Office 2010 - MS 2003 has only COUNTIF, then try some of other commented formulas.
0

Author Commented:
rorya: I got 0 for my count
0

Author Commented:
Here is an attached excel sheet.... Count.xls
0

Commented:
This should work...
=COUNTIFS(B1:B50;"QP*";C1:C50;"00075")
0

Author Commented:
hitsdoshi1:  Negative...I got an error
0

Author Commented:
Thanks Rorya!  Works like a charm!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.