# COUNTIFS NOT IN from a country list

#### Excel_beginner

##### New Member
Hello friendly community.

I have a list of countries and the number of participants from each of those countries. I wish to make a new calculation of participants where some countries are excluded from the calculation and categorized in a new group. Countries that I want to calculate the number of participants for in my new group called "other" are all EXCEPT Argentina, Brazil and Colombia.
What I want to achieve is shown in columns New_country and # New_participants.
How can I write a COUNTIFS function for this?
 Country # Participants New_country # New_participants Argentina 3 Argentina 3 Bolivia 3 Brazil 2 Brazil 2 Colombia 6 Chile 3 Other 22 Colombia 6 Ecuador 3 Paraguay 4 Peru 3 Uruguay 2 Venezuela 4

Last edited:

#### pecoflyer

##### Well-Known Member
Hi
I fail to understand, although the forums recommends it to make things easier for everyone, why many OP's do not post a sample sheet as recommended...
Anyway, attached is a possible solution ( there are probably many others and I suspect there will be other requirements here)

#### Attachments

• 12.8 KB Views: 3

#### Excel_beginner

##### New Member
Hi
I fail to understand, although the forums recommends it to make things easier for everyone, why many OP's do not post a sample sheet as recommended...
Anyway, attached is a possible solution ( there are probably many others and I suspect there will be other requirements here)
Maybe it was unclear. Let me clarify.
There are 10 countries. The thing I want to achieve by COUNTIFS NOT IN is shown in column # New_participants.
I wish to merge together some of the countries into a single category called "other". I think I was very clear on that point.
So basically, I want the other category to count/include the number of particapants where country NOT in Argentina, Brazil or Colombia.
Is it clearer now?

#### pecoflyer

##### Well-Known Member
I never implied that your explanation was unclear. As a matter of fact I presented to you a possible solution which you do not seem to have read.
My point is that you did not bother to post a sample sheet as recommended, not a picture

#### ETAF

##### Member
an alternative
=IF(C2="other",SUM(\$B\$2:\$B\$11,-(SUMIFS(\$B\$2:\$B\$11,\$A\$2:\$A\$11,\$I\$2:\$I\$8))),SUMIF(\$A\$2:\$A\$11,C2,\$B\$2:\$B\$11))
where I is an list of excluded countries

Or you could use
=IF(C2="other",SUM((SUMIFS(\$B\$2:\$B\$11,\$A\$2:\$A\$11,\$G\$2:\$G\$8))),SUMIF(\$A\$2:\$A\$11,C2,\$B\$2:\$B\$11))
with an include list

#### Attachments

• 11.2 KB Views: 4

#### Peter Bartholomew

##### Well-Known Member
Turned out much the same as the @ETAF except this is designed to return a dynamic array.
Code:
``````= LET(
counts, SUMIFS(participants, country, selected),
others, SUM(participants) - SUM(counts),
IF(selected<>"other", counts, others)
)``````