# Thread: count if - using macro? (EXCEL 2000)

1. ## count if - using macro? (EXCEL 2000)

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

2. ## Re: count if - using macro? (EXCEL 2000)

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).

3. ## Re: count if - using macro? (EXCEL 2000)

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?

4. ## Re: count if - using macro? (EXCEL 2000)

Thanks for the quick response Hans,

Much appreciated.

5. ## Re: count if - using macro? (EXCEL 2000)

macropod

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

Thanks again for the response.

6. ## Re: count if - using macro? (EXCEL 2000)

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?

7. ## Re: count if - using macro? (EXCEL 2000)

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).

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•