How to Reference an Array to Count Values?

[Mashuganah]

I need to reference a list of names in a column in one worksheet to count the
number of times those names occur in a column in another worksheet in the
same workbook.

That is, check the name list and sum the number of times those names occur
in another list.

How do I do this?

 

[Jim Thomlinson]

Take a look at the sumif function in help.

 

[Mashuganah]

Thenks Jim. The problem is that the help file does not give an example of
how to structure the equation to compare against multiple text variables.
Help gives an example of using a single text variable and gives the
impression that I would have to enter each name in the equation which would
obviate the use of an array.

How do I structure a sumif equation to get the result I need?

 

[Jim Thomlinson]

Sorry I could have been more clear. In general you will have one countif
function for each name in your source list. You will then just need to sum up
all of the individual counts... If there are 10 names in your list of source
names then you will need 10 countif functions and one sum function to add all
of the counts.

 

[Mashuganah]

Ugh! I was hoping to avoid such a long and difficult to maintain equation.
I've been looking at countifs, lookup, choose, sumifs, match, and sumproduct.
I had hoped there was a way to make Excel do the heavy lifting of walking
through a list and comparing the values it contains to another list and
summing the number of occurrences.

 

[Jim Thomlinson]

In one fell swoop is possible but normally I like to post formulas that can
be followed by the average user first. Usually taht covers it. Here is the
all in one formula...

=COUNT(MATCH(C1:C35, A1:A2, 0))

Which is an array formula and **MUST** be committed with Ctrl+Shift+Enter.
If it was committed correctly XL will place { } around the formula for you.

A1:A2 is the source list of names
C1:C35 is the data list that I want counted.

 

[Mashuganah]

Thanks Jim! That works!

In one fell swoop is possible but normally I like to post formulas that can
be followed by the average user first. Usually that covers it. Here is the
all in one formula...

=COUNT(MATCH(C1:C35, A1:A2, 0))

Which is an array formula and **MUST** be committed with Ctrl+Shift+Enter.
If it was committed correctly XL will place { } around the formula for you.

A1:A2 is the source list of names
C1:C35 is the data list that I want counted.