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


SOUTHAMERICA70Asked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor 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
 
kaapsliicCommented:
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
 
hitsdoshi1Commented:
=CountIf(B1:B50,"QP*")+CountIf(C1:C50,"00075")

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
SOUTHAMERICA70Author Commented:
kaapsliic:  Thanks but I could not get the formula to count at all.
0
 
Rory ArchibaldCommented:
=SUMPRODUCT((B1:B100="QP")*(C1:C100="00075"))
should do it.
0
 
Rory ArchibaldCommented:
COUNTIFS will only work in 2007 or later - in 2003 or earlier you need SUMPRODUCT.
0
 
SOUTHAMERICA70Author 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
 
Rory ArchibaldCommented:
PS Or a SUM(IF(...)) array formula but SUMPRODUCT is more efficient.
0
 
kaapsliicCommented:
Jap, that's in MS Office 2010 - MS 2003 has only COUNTIF, then try some of other commented formulas.
0
 
SOUTHAMERICA70Author Commented:
rorya: I got 0 for my count
0
 
SOUTHAMERICA70Author Commented:
Here is an attached excel sheet.... Count.xls
0
 
hitsdoshi1Commented:
This should work...
=COUNTIFS(B1:B50;"QP*";C1:C50;"00075")
0
 
SOUTHAMERICA70Author Commented:
hitsdoshi1:  Negative...I got an error
0
 
SOUTHAMERICA70Author 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.

All Courses

From novice to tech pro — start learning today.