Count All other countries

Question

Every month I have to compile a count of all countries received that month. Specific countries are counted individually for occurence, and the rest goes into a count for "other".

For example I might receive a list of countries as such:
"my, my, ch, de, de, fr, fi, ch, fr, fr, gb, fr, fr, sg, sg, sg, my, gr, es, it, nl, gb, at, us, at, ie, pt, my, gr, ru, ru"

where cn, my, de, sg, are counted in their own individual rows,

and anything that isn't cn, my, de, or sg are counted as other.

I have set up countif for the individual countries counted, however how to I create the "other". I have tried countif <> to exclude cn, my, de, sg however I keep getting an error message. I also tried doing a COUNTA for the whole list, and then tried to deduct the countries that are NOT other, but would still get an error message. I'm not sure how to set up the formula.

Answer 1

Do you get "my, my, ch, de, de, fr, fi, ch, fr, fr, gb, fr, fr, sg, sg, sg, my, gr, es, it, nl, gb, at, us, at, ie, pt, my, gr, ru, ru"

as string in one cell? Does that string include the ""?

Or do you get each separated in rows?

Andreas.

Answer 2

Hi daph-826B190C-F9E5-444D-ACDE-C145365F2227,

I'm glad to know that your work in Excel is proceeding smoothly. If you find my response helpful, please click "Yes" or "No" under my reply. This feedback will not only assist other users facing similar issues in finding the solution more quickly but also help us optimize your experience. Should you encounter any further problems, feel free to post questions in the community at any time.

Wishing you all the best.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist

Answer 3

Hmm, I tested the same function in Excel, and it seems the issue is with google sheets. Works perfectly in Excel. Thanks for your help.

Answer 4

Thanks Jonathan Z.

That's where I'm stuck actually. How do I do the countif for the excluded countries? This is what I tried

=COUNTIF(D2:D45,{”cn”,”de”,”fr”,”gb”,”nl”,”au”,”us”,”ir”,”se”,”jp”,”in”,”fi”,”kr”,”ca”,”it”,”ch”,”dk”,”ru”,”no”,”es”,”nz”,”pl”,”cz”,”sg”,”id”,”ph”,”th”,”mm”,”vn”,”my”})

But it's incorrect (gives parse error). So far I've only been able to do countif for just 1 country at a time.

I also tried

=SUM(COUNTIFS(D2:D45,{"sg","my","de"}))

But it still only gives me the count of the first country.

Answer 5

Hi daph-826B190C-F9E5-444D-ACDE-C145365F2227,

Thanks for visiting Microsoft Community.

Based on your request, I conducted a test, and the results are as follows. Given that you have already set up country-specific counts using the COUNTIF function in your spreadsheet, you can calculate the 'Total' by using =COUNTA(A:A). To determine the number for 'Others', simply subtract the sum of these specific countries' counts from the total count.

Should you have any further inquiries or need additional assistance, please feel free to reply at your convenience.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist