Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    Thanks for the quick response Hans,

    Much appreciated.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #6
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •