How to count number of occurences

Question

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.

Answer 1

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:

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

For example:

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

Answer 2

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

Formula in D1:

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

Note: Enter through CtrlShiftEnter

Answer 3

Easy way would be using COUNTIFS:

COUNTIFS function

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):

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.

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

Adapt it to your needs.

Answer 4

Maybe SUMPRODUCT will help:

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