CODEWITHSUNDEEP
excelformulacountif
Icy
Icy

Reputation: 41

Excel COUNTIFS value not in a list

I'm trying to count rows when values of a column are equal to a specific value AND, at the same time, a value of an another column is not in a list.

For example imagine the following table :

A  B       C
ID COUNTRY COLOR
1  GER     blue
2  GER     green
3  FRA     blue
4  USA     red 
5  GER     red
6  FRA     blue
7  GER     green
8  FRA     red
9  GER     gold

I Would like to count each rows where:

I tried the following formula:

=SUM(COUNTIFS(B:B;"GER;C:C{"<>red";"<>blue"}))

I was expecting 3 because I would like to count rows where the country is "GER" and color is everything except red and blue (Line 2, 7 and 9).

BUT output is 8. This is surely because Excel detects 4 lines where the country is GER and color not red (1,2,7,9) + 4 lines GER and color not blue (2,5,7,9).

I know it is not complicated, but I can't figure it out. Maybe one of you could give me a hint on how deals with my problem? Thanks a lot.

Upvotes: 0

Views: 8429

Answers (1)

Michael Wycisk
Michael Wycisk

Reputation: 1695

I am not sure why you combine SUM and COUNTIFS. A simple COUNTIFS formula with 3 criteria should be enough. You can use this formula:

=COUNTIFS(B2:B10;"GER";C2:C10;"<>red";C2:C10;"<>blue")

The result is 3 (GER-green, GER-green and GER-gold).

COUNTIFS in Excel

Upvotes: 5

Related Questions