How to count multiple values in an Excel formula.

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

@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.

Glenn Ray

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.

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.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Glenn Ray

^Yep. Just a minor correction (close bracket):
=SUM(COUNTIF(A12:A400,{"02","03"}))

Glenn,
Thanks for catching my goof on the second formula. I edited it to read correctly.
Brad

Good one Brad! :)

maximus1974

ASKER

Why do I keep getting the error attached?
Capture.PNG

I can reproduce your error message by using two single-quote characters instead of the required double-quote character in the array constant.

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"}))

Yeah that might be the case as well.

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"}))

maximus1974

ASKER

I keep getting the same error wen copying it directly from the code snippet. Two screenshots attached.
Capture.PNG
Capture2.PNG

See which one of the following works for you...

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

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

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