Link to home
Start Free TrialLog in
Avatar of SOUTHAMERICA70
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


Avatar of kaapsliic
kaapsliic
Flag of Latvia image

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
Avatar of hitsdoshi1
hitsdoshi1

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

Avatar of SOUTHAMERICA70

ASKER

kaapsliic:  Thanks but I could not get the formula to count at all.
Avatar of Rory Archibald
=SUMPRODUCT((B1:B100="QP")*(C1:C100="00075"))
should do it.
COUNTIFS will only work in 2007 or later - in 2003 or earlier you need SUMPRODUCT.
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.
rorya: I got 0 for my count
Here is an attached excel sheet.... Count.xls
This should work...
=COUNTIFS(B1:B50;"QP*";C1:C50;"00075")
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
hitsdoshi1:  Negative...I got an error
Thanks Rorya!  Works like a charm!