• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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# ParticipantsNew_country# New_participants
Argentina3Argentina3
Bolivia3Brazil2
Brazil2Colombia6
Chile3Other22
Colombia6
Ecuador3
Paraguay4
Peru3
Uruguay2
Venezuela4
 
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

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

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)
  )
81173
 
Top