SUM COUNTIFS with multiple criteria

**john_london** · 02-28-2011, 11:04 AM

Hi guys -

So I tried the following formula but it is giving me the wrong count:

Please Login or Register  to view this content.

I'm trying to get the counts of Group 1 + Group 2 where their status is Current, Proposed, or Dormant. The SUM/COUNTIFS formula seems to not work - can you only have one {} in a sum countif? Is there a better way of doing this?

Thanks!
John

**Marcol** · 02-28-2011, 11:11 AM

Your profile indicates you are using 2003.
The Countifs Function is not available in 2003, it was introduced in 2007.

What version of Excel are you using?

**john_london** · 02-28-2011, 11:13 AM

Sorry - 2007. We were upgraded but I didn't update this profile... will do now!

Thanks

**daddylonglegs** · 02-28-2011, 11:29 AM

This subtle change should make it work

=SUM(COUNTIFS(Tracker!$CI$3:$CI$3000, {"Group 1","Group 2"}, Tracker!$G$3:$G$3000,{"Current";"Proposed";"Dormant"}))

Note semi-colons rather than commas in the second "array constant"

In general if you have two "arrays" with { and } then one should have commas, the other semi-colons (it doesn't matter which way round). A third one won't work. For a version that would deal with any number try

=SUMPRODUCT(ISNUMBER(MATCH(Tracker!$CI$3:$CI$3000, {"Group 1","Group 2"},0))*ISNUMBER(MATCH(Tracker!$G$3:$G$3000,{"Current","Proposed","Dormant"},0)))