How to count multiple values in an Excel formula.

What is the proper syntax in an Excel formula to count multiple values.

Ex. I want to know the amount records that appear with both 02 and 03 in the same column. I currently have the formula only counting 02.

Screenshot attached.
Capture.PNG
maximus1974Asked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
You can pass an array to COUNTIF and use SUM to add up the results. The resulting formula does not need to be array-entered. Note the use of curly braces { } surrounding "02" and "03". Those curly braces create an array. COUNTIF returns the count for each array element, then SUM adds them up.
=SUM(COUNTIF(A12:A400,{"02","03"}))

Since COUNTIF counts both text and numbers (converting the text into numbers if that is possible), you can simplify even further:
=SUM(COUNTIF(A12:A400,{2,3}))
2
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
You may try this....
=COUNTIF(A12:A400,"02")+COUNTIF(A12:A400,"03")

Open in new window


OR simply this...
=SUMPRODUCT(--(A12:A400={"02","03"}))

Open in new window

1
 
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
I like the sum of the two COUNTIF functions.  Here's another possible solution using an array function:
{=SUM(IF($A$12:$A$400={"02","03"},1,0))}

Use [Ctrl]+[Shift]+[Enter] to enter the formula.

-Glenn
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Glenn
If you use Sumproduct as suggested by me, you don't need to place an Array formula that is where Sumproduct has an edge over Sum function as Sumproduct can handle arrays.
0
 
Glenn RayExcel VBA DeveloperCommented:
I'm not suggesting that the array function is any better then SUMPRODUCT, just that it is another alternative. Frankly, the COUNTIF solution you offered is the simplest and best, IMO.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Agree. Countif is good if there are only few criteria but if there are multiple criteria, it would look ugly to join multiple Countif functions. So obviously Sumproduct/Sum would be a good alternative in that case and easy to tweak as well.
0
 
Glenn RayExcel VBA DeveloperCommented:
^Yep.  Just a minor correction (close bracket):
=SUM(COUNTIF(A12:A400,{"02","03"}))
0
 
byundtCommented:
Glenn,
Thanks for catching my goof on the second formula. I edited it to read correctly.
Brad
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Good one Brad! :)
0
 
maximus1974Author Commented:
Why do I keep getting the error attached?
Capture.PNG
0
 
byundtCommented:
I can reproduce your error message by using two single-quote characters instead of the required double-quote character in the array constant.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It seems you missed the SUM part of the formula and also I guess you should use ; (semi-colon) instead of comma as per your regional settings.
Try this to see if that works for you.....

=SUM(COUNTIF(A12:A400;{"02";"03"}))

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Yeah that might be the case as well.
0
 
byundtCommented:
maximus1974 has an IP address in New York City, so commas are the correct list separator. I am betting the problem is just the single quote instead of double quote.

If the formula is copied from the code snippet below and pasted in maximus1974's workbook, it should work without error.
=SUM(COUNTIF(A12:A400,{"02","03"}))

Open in new window

0
 
maximus1974Author Commented:
I keep getting the same error wen copying it directly from the code snippet. Two screenshots attached.
Capture.PNG
Capture2.PNG
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
See which one of the following works for you...

=SUM(COUNTIF(A12:A400|{"02","03"}))

Open in new window

OR
=SUM(COUNTIF(A12:A400|{"02"|"03"}))

Open in new window

0
 
byundtCommented:
maximus1974,
If you use a US version of Excel, I strongly suggest that you change your list separator character to a comma. I've never seen anyone use a pipe symbol like shown in Capture2.PNG in the Region and Language...Customize Format...Numbers control panel--and if you post questions on an Excel forum, everybody assumes that you use a comma as the list separator.

Brad
1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The chosen answers resolved the question.
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.