• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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


0
SOUTHAMERICA70
Asked:
SOUTHAMERICA70
  • 6
  • 4
  • 2
  • +1
1 Solution
 
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
 
SOUTHAMERICA70Author Commented:
kaapsliic:  Thanks but I could not get the formula to count at all.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
Rory ArchibaldCommented:
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
 
SOUTHAMERICA70Author Commented:
hitsdoshi1:  Negative...I got an error
0
 
SOUTHAMERICA70Author Commented:
Thanks Rorya!  Works like a charm!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now