How do I count the frequency of items in a non-numeric list?
 

I have a list of employees (in Excel 2002) who are working in many different
countries. I want to calculate how many are in each country. How do I do
this?

How do I count the frequency of items in a non-numeric list?
 

I was hoping there was a formula (like a histogram) that would count the
countries so I wouldn't have to enter each country name individually into a
formula. I would only need to count the data in one column (the country
column) since the countries are entered multiple times (one line per
employee). (e.g., something that would count 4 occurences of Canada, 2 of
Belgium etc in this one column). Unfortunately, a histogram won't count
non-numeric text. Any ideas? RAR

"Dave F" wrote:

Use SUMPRODUCT

Assume employee name is in column A and country is in column B

=SUMPRODUCT((A1:100)*(B1:B100="USA")) would count the number of employees
that are in the USA. Replace "USA" with the country of your choice, and
adjust the ranges to suit your data.

Dave
--
Brevity is the soul of wit.


"RAR" wrote:

I have a list of employees (in Excel 2002) who are working in many different
countries. I want to calculate how many are in each country. How do I do
this?

How do I count the frequency of items in a non-numeric list?
 

You could use a PivotTable, putting the country on the left side and
just having a count for the actual data.

Alternatively, you could use the advanced filter
(Data-Filter-Advanced Filter) to generate a static list of each
individual country, and then use a COUNTIF() on your full list for each
country. To use the advanced filter, specify the data range to be
just the column with the country names, make sure to choose Copy To
Another Location and specify the location, and also check Unique
Records Only button.

Scott

RAR wrote:
I was hoping there was a formula (like a histogram) that would count the
countries so I wouldn't have to enter each country name individually into a
formula. I would only need to count the data in one column (the country
column) since the countries are entered multiple times (one line per
employee). (e.g., something that would count 4 occurences of Canada, 2 of
Belgium etc in this one column). Unfortunately, a histogram won't count
non-numeric text. Any ideas? RAR

"Dave F" wrote:

Use SUMPRODUCT

Assume employee name is in column A and country is in column B

=SUMPRODUCT((A1:100)*(B1:B100="USA")) would count the number of employees
that are in the USA. Replace "USA" with the country of your choice, and
adjust the ranges to suit your data.

Dave
--
Brevity is the soul of wit.


"RAR" wrote:

I have a list of employees (in Excel 2002) who are working in many different
countries. I want to calculate how many are in each country. How do I do
this?