Countifs function with multiple criteria in same column

**sam99** · 08-27-2012, 11:36 PM

Hello,
I have spreadsheet Containing data as shown below.
Country Government Data Grade Type
India Democracy H B L1
China Communist H A D2
USA Democracy M A D2
UK Democracy L B L2
Canada Democracy M C D1
Russia Communist M A D3

I want to count the Countries belonging the Democratic group having data type of H or M and Grade A
I want to use Countifs (government,democracy, Data, or(H,M), Grade,A) as is but or it looks like i cannot use the OR function as shown in the formula written. Can u tell me if one can use or statement within a countif fnuction

Appreaciate the help.

**vlady** · 08-27-2012, 11:44 PM

maybe like this

Countifs (government,democracy, Data, {"H","M"}, Grade,A)

**sam99** · 08-28-2012, 08:59 AM

Does not work. Thanks for the suggestion. the only way I have found out is to use countifs twice ie (Counitfs for H + Countifs for M) However, it becomes cumbersome once u have multiple columns with more than one criteria.

**daddylonglegs** · 08-28-2012, 09:03 AM

Vlady's formula works if you add a SUM function like this

=SUM(COUNTIFS(government,"Democracy",Data,{"H","M"},Grade,"A"))

If you have another column with multiple criteria e.g. grade = "A" or "B" or "C" then you need to use ; separator for that second one only, i.e.

=SUM(COUNTIFS(government,"Democracy",Data,{"H","M"},Grade,{"A";"B";"C"}))

Any more than 2 multi-criteria ranges and you probably need SUMPRODUCT instead....