?
Solved

How to count multiple values in an Excel formula.

Posted on 2016-09-23
18
Medium Priority
?
63 Views
Last Modified: 2016-10-16
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
0
Comment
Question by:maximus1974
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 3
  • +1
18 Comments
 
LVL 32

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 668 total points (awarded by participants)
ID: 41813072
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
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 664 total points (awarded by participants)
ID: 41813193
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
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41813394
@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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41813429
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
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41813434
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
 
LVL 81

Accepted Solution

by:
byundt earned 668 total points (awarded by participants)
ID: 41814801
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41814819
^Yep.  Just a minor correction (close bracket):
=SUM(COUNTIF(A12:A400,{"02","03"}))
0
 
LVL 81

Expert Comment

by:byundt
ID: 41814823
Glenn,
Thanks for catching my goof on the second formula. I edited it to read correctly.
Brad
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41814827
Good one Brad! :)
0
 

Author Comment

by:maximus1974
ID: 41816030
Why do I keep getting the error attached?
Capture.PNG
0
 
LVL 81

Expert Comment

by:byundt
ID: 41816053
I can reproduce your error message by using two single-quote characters instead of the required double-quote character in the array constant.
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41816056
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
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41816059
Yeah that might be the case as well.
0
 
LVL 81

Expert Comment

by:byundt
ID: 41816079
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
 

Author Comment

by:maximus1974
ID: 41818031
I keep getting the same error wen copying it directly from the code snippet. Two screenshots attached.
Capture.PNG
Capture2.PNG
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41818229
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
 
LVL 81

Expert Comment

by:byundt
ID: 41818487
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
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41845500
The chosen answers resolved the question.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question