1

enter image description here

How can I write formula that counts how many John and James are in my column that are from USA. The answer for this example will be 2 (John-USA and James-USA)

Please note that this example is simple. I may have 100000 rows and 1000 names.

Improve this question
2
  • You can use 2 COUNTIFS, 1 for John-USA and 1 for James-Usa, and sum them up Commented Aug 22, 2019 at 11:06
  • Sounds like a job for a pivot table, though countifs can handle the specific case. Commented Aug 22, 2019 at 11:06

4 Answers 4

Reset to default
4

You'll need to refer to a list of names you want to include as some sort of OR operator:

enter image description here

Formula in D1:

=SUM(COUNTIFS(A1:A6,C1:C2,B1:B6,"USA"))

Note: Enter through CtrlShiftEnter

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Maybe SUMPRODUCT will help:

=SUMPRODUCT((A1:A6&B1:B6="JohnUSA")+(A1:A6&B1:B6="JamesUSA"))

enter image description here

Improve this answer

5 Comments

What if I want to count 50 names? I can't write 50 +'s
Please be very careful concatenating ranges. It's something I would highly recommend to avoid. What if someone is called LUU from South Africa SA. Would the concatenated value be "LUUSA" as Lu from the USA or Luu from SA (maybe a terrible example namewise, but you get the idea :) )
That's a very fair point! Didn't think about that. Thanks @JvdV I guess I would need to introduce some sort of delimiter to take care of it.
@JustynaMK A delimiter won't necessarily work in all cases. The only sure safe way is to treat them as separate entries. Here is another question where one of the answers uses concatenation instead of treating the values separately. It could also be an issue with MATCH, not just SUMPRODUCT. Again most times it makes no difference, but it is something you should be aware of. Still a good answer, I upvoted.
Many thanks @ImaginaryHuman072889, that's another useful lesson for me. Much appreciated.
2

Easy way would be using COUNTIFS:

COUNTIFS function

enter image description here

The formula I've used in D2is:

=COUNTIFS($A$1:$A$6;"John";$B$1:$B$6;"USA")+COUNTIFS($A$1:$A$6;"James";$B$1:$B$6;"USA")

UPDATE: If you need to count several names at once from several countries, use a Pivot Table:

I did a dataset like this (note I added headers):

enter image description here

Create a Pivot Table based on data:

  1. Take NAME and COUNTRY to FILTERS section
  2. Take NAME again, but into VALUES section, and make sure the operation is COUNT

Playing with the filters you can choose 1 or more countries (or even all) and also 1 or more names (or even all) and the Pivot Table will calculate the result.

enter image description here

After applying filters COUNTRY=USA and NAME=John OR JAMES I get as result 2:

enter image description here

Adapt it to your needs.

Improve this answer

1 Comment

What if I want to count 50 names? I can't write 50 +'s
2

Based on your common complaint "What if I want to count 50 names? I can't write 50 +'s", you must specify somewhere the list of names to count (whether hardcoded in the formula [in that case you will need 50 +'s], or in a list somewhere in your workbook). Otherwise, how can your formula magically know which 50 names to look for?

My suggestion is to make a separate list, count the entries for each item in the individual list, then sum them all up at the end.

For example:

enter image description here

Also possible to perform the sum of the counts all at once:

For example:

= SUMPRODUCT(COUNTIFS(A:A,D2:D3,B:B,E2:E3))
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.