Counting Occurrences in a List

[Domenic]

Hi Rick,

Assuming that your list of names are in Sheet 1, Column A, and start in
Row 2, put the following formula in A2 of Sheet 2 and copy down until
you get zero:

=INDEX(Sheet1!$A$2:$A$1000,MATCH(0,COUNTIF(Sheet2!$A$1:A1,Sheet1!$A$2:$A$
1000),0))

entered using CTRL+SHIFT+ENTER.

Then put this formula in B2 of Sheet 2 and copy down:

=COUNTIF(Sheet1!$A$1:$A$1000,A2)

Hope this helps!

 

[Rick Gregory]

I have a list of about 800 items and growing. I need to count how many
times an individual's name appears in the list. I have sorted by last name,
and want to be able to do a quick count of how many times the name "Brown"
"Smith" etc appear.

The list is updated weekly (these are entries in a sweepstakes contest), so
doing this manually is no good.

I've looked at the countif function, but there are at least 40 different
names appearing in the list, so I don't know how to make that work.

Any ideas? (Excel 98)

 

[Rob]

I have a list of about 800 items and growing. I need to count how many
times an individual's name appears in the list. I have sorted by last name,
and want to be able to do a quick count of how many times the name "Brown"
"Smith" etc appear.

The list is updated weekly (these are entries in a sweepstakes contest), so
doing this manually is no good.

I've looked at the countif function, but there are at least 40 different
names appearing in the list, so I don't know how to make that work.

Any ideas? (Excel 98)

Another approach...

Click any cell in your list, point to Data > Filter > AutoFilter
In the appropiate column select the name you look for
Down below on the left - just below the Sheet1 - Sheet2 - etc tabs you
will read x of 800 records found

No formulas needed - just a feature most people miss

It is important that your list has no empty rows. Empty rows split your
list and AutoFilter will only filter the top list.

Hope this helps - Rob

 

[Domenic]

I did as you instructed. In column A of Sheet2, I'm getting #N/A as a
result in every cell where I enter the formula. This yields "0" in all the
Column B cells.

Also, I'm note sure what you meant by: "entered using CTRL+SHIFT+ENTER."

Normally, when you enter a formula you hit the ENTER key to confirm.
With this type of formula, instead of hitting the ENTER key only, you
have to press all three keys together ----> CTRL+SHIFT+ENTER.

You'll notice that Excel includes braces {} around the formula to
indicate that it's an array formula.

Not entering the formula in this manner would explain why you're getting
#N/A in every cell.

Also, your formula refers to cell range $A$1:A1, even though A1 is empty.
Is this relevant?

Yes. That cell needs to be empty in order for the formula to work.

 

[Rick Gregory]

Domenic-

Works now. Thanks.

Rob-

Thank you too.

I also found the SUBTOTAL tool works for this as well.

Best regards,
Rick

 

[Domenic]

Domenic-

Works now. Thanks.

You're welcome, Rick!

 

[Rick Gregory]

Domenic-

Thank you for the help. Unfortunately it's not working yet!

I did as you instructed. In column A of Sheet2, I'm getting #N/A as a
result in every cell where I enter the formula. This yields "0" in all the
Column B cells.

Also, I'm note sure what you meant by: "entered using CTRL+SHIFT+ENTER."

Also, your formula refers to cell range $A$1:A1, even though A1 is empty.
Is this relevant?

Any further suggestions? Many thanks.

-Rick