count if – using macro? (EXCEL 2000)

Topic

New Reply

Good afternoon all,

Attached is a VERY small extract of a list of numbers that would be keyed in col A. This list could extend all of Col. A. — Hopefully as you can see I want to get a count of how many times a particular number shows up in this list. I would also like to show that list of numbers along row 1 starting at col c. Is there a macro that can be produced to do this same thing. I am looking to only show the number in Row 1 one time (ie) 3445 shows as being in the column A – 2 times.

Thanks in advance for the help

Reply | Quote

Replies

You can use a pivot table for this. I inserted a column heading in A1, then created a pivot table based on A1:A1000.
It displays (empty) too. You can hide this by right-clicking it and selecting Hide from the popup menu.

When the data change, click anywhere in the pivot table and click the Refresh button on the Pivot Table toolbar.

See attached version (captions may be in Dutch because I’m using the Dutch language version of Excel).

Reply | Quote

Thanks for the quick response Hans,

Much appreciated.

Reply | Quote

Hans,

I have a problem…. you said “When the data change, click anywhere in the pivot table and click the Refresh button on the Pivot Table toolbar.’ However, when I added a couple of numbers to col A in your exmple and then placed my cursor on and pressed “Refresh data” the whole table went blank ! what did I do wrong?

Reply | Quote

Sorry, no idea – when I add, remove or edit data in column A, then click in the pivot table and click the Refresh button (the red exclamation mark), the pivot table is updated correctly.
You might try re-creating the pivot table from scratch. The field name should go both into the Column area and into the Data area (with Count as summary function).

Reply | Quote

Hi,

Hans has already given you one solution. Here’s another:

Insert the array formula
=MIN(IF($A$1:OFFSET($A$1,COUNT($A:$A)-1,)>B1,$A$1:OFFSET($A$1,COUNT($A:$A)-1,)))
in C1 and copy that and your formula in C2 across as far as needed.

With both solutions, no macros have been needed. Are you sure you need a macro-based solution?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

macropod

No, I do not NEED a macro …. these will do just fine.

Thanks again for the response.

Reply | Quote