COUNTIFS NOT IN from a country list

[Excel_beginner]

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

 

[pecoflyer]

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)

 

[Excel_beginner]

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]

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]

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

 

[Peter Bartholomew]

Turned out much the same as the @ETAF except this is designed to return a dynamic array.

= LET(
    counts, SUMIFS(participants, country, selected),
    others, SUM(participants) - SUM(counts),
    IF(selected<>"other", counts, others)
  )