Formuli on filtered data

Topic

New Reply

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

Reply | Quote

Replies

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 This Post 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

 Selection.CurrentRegion.Select
 NumRows = Selection.Rows.Count

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote