Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formuli on filtered data

    Hi there,
    EXCEL 97
    Can anyone please help me with counting the records on filtered data with a macro. The problem is that the range of data differs everytime the user filters the worksheet. When you record a macro, the formula in VB coding records the specific cells of your first selection. How can I make a flexible range known in a formula. (Like the {end}{down} in Lotus macros.)

    Please help me out.
    THANKS
    Rene

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formuli on filtered data

    If the formulae are set up correctly on the sheet, and the underlying data remains the same then there should be no need to use a macro each time the list is filtered differently. See <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=28951&page=0&view =expanded&sb=5>This Post</A> for ideas on subtotals. You should always use the subtotal function rather than SUM or COUNT. Check the help if you need assistance with it.

    However if your data range does change you could possibly try the following. With the cursor placed inside the data range, the following code should give you the last row number of the data
    <pre> Selection.CurrentRegion.Select
    NumRows = Selection.Rows.Count
    </pre>

    You could then place your formulae in the following row.
    It is always important to filter the data before entering the formulae. Another point worth remembering is that the subtotals can be placed in a row above the data, that way they never change. You could then have your headings on row 2 and the data underneath.

    Andrew C.

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formuli on filtered data

    Hi there Andrew,
    Thanks a lot for the reply, however I still have the problem. I cannot use subtotals as I am counting alpha fields. The data range also changes after every filter. Therefore, what my idea is, is to put a counter for the number of lines in a text box. At the moment I am using =counta to count the lines. How can I get the filter counter which is at the bottom of the screen after a data filter (ie: 20 out of 240 records), in a text box?

    Cheers
    Rene

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formuli on filtered data

    Rene,

    Insert a Text Box, and while you still have it as the selection, go to the formula bar and enter a reference to the cell, e.g. =A100.

    It is probably wiser to apply a name to the cell with the formula, and then = NAME where NAME is the name you gave the cell.

    Andrew

  5. #5
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formuli on filtered data

    Hi there again,
    Thanks, that piece works 100%. I still have a problem with the original formula. Let me explain.
    line 1 name address
    line 2 andrew street 1
    line 3 rene street 2
    line 4 xxx xxx
    line 5 xxx yyy

    my original formula works on all the lines =@counta(a2.a5)
    now I filter on name = xxx
    now I want my formula to change automatically to =@counta(a4.a5).
    the result in my inputbox before the filter should = 4 and after the filter should = 2

    Thanks again and enjoy your day, (but answer me before you enjoy it too much)

    Rene

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formuli on filtered data

    Hi Rene,

    I am assuming you are using the auto filter, i.e. you have dropdown buttons on all the field headers.

    Try the following :

    1. Remove any existing filter
    2. Now filter the name NOT equal to some name you know is not there
    3. Go to the bottom of list, and select the cell where you want the result to show.
    4. Now click on the auto sum button (sigma sign), that should give you a formula =subtotal(9,.....).
    5. Change the 9 to a 3 and you should have a count of the data . (Subtotal(3,...)
    6. Filter and test.

    Let me know if that works, otherwise I will not enjoy my day.

    Andrew

  7. #7
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formuli on filtered data

    Hi, me again,
    Two things we must keep in mind is that I cannot use the auto sum because I want to count text data, not numeric data, and the other thing is I want to automate the count as I don't want the users to change the formula manually, so whatever the filtered range is, must be the range in the formula (or vb coding) After the users filtered the data, they must immediately see how many records are in their filtered selection. oh yes, you are correct, I am working with the auto filter.

    This is fun sorting things out together. Thanks
    Rene

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formuli on filtered data

    Well lets see if we can have some more fun.

    Select the column you want to apply the count formula to. Give it a name like Names. In a cell far away type =SUBTOTAL(3,Names). Change the filter and criteria and see if it updates (it should). There should be no problem with counting text items. Just try it and if it does not work let me know.

    Andrew

  9. #9
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formuli on filtered data

    You are such a star. This is exactly what I wanted. Thanks a lot and now you can enjoy the day, I'll stay off your back for a while.

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formuli on filtered data

    Looking at the solutions offered so far, I'm surprised I havn't seen anyone mention countif. This, and sumif, are a couple of my favourite ways of grouping data without having to sort or subtotal.

    For the simplest counts, assuming cell B32, =countif(b1.b30,"name") will give you a count of all items in the range matching criteria "name". For a slightly more flexible count, you could substitute A32 for "name", and then just type in the new criteria, rather than having to edit the formula every time you want to look at a different subset of data.

  11. #11
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formuli on filtered data

    Because the topic was regarding 'filtered' data. See the below excerpt from the Excel 8 Help on Subtotal:


    SUBTOTAL will ignore any hidden rows that result from a list being filtered. This is important when you want to subtotal only the visible data that results from a list that you have filtered.

Posting Permissions

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