How to Reference an Array to Count Values?

  • Thread starter Thread starter Mashuganah
  • Start date Start date
M

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?
 
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?
 
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.
 
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.
 
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.
 
Thanks Jim! That works!


Jim Thomlinson said:
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.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to refer to tab names? 1
Counting by Two Columns of Variables 9
Excel Copy cells from one worksheet to another workseet - increment row & do again 0
VBA - retrieve data on last create named range from previous inserted named range 0
Formula that sums/counts across worksheets with variable reference 1
Counting unique values in a table 6
copy worksheet and automatically name 4
Matching records based on a common field 1

Back
Top