MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula & Filtering

Posted by DJ on March 21, 2001 9:00 PM

Once I have filtered a column's data, the total calculation that I have created still shows me the total number of x's in the column. I would like it to only show me the x's that are not hidden with a recalculation after the macro runs. Can anyone help?

Posted by Fizzy on March 22, 2001 3:17 AM


Have a look at the SUBTOTAL function. Think that it will do what you want.

The fastest way to enter the correct formula is to filter your list and then hit the autosum button. Because you have data filtered excel will use the subtotal function and default function_num to "9" for SUM. Change 9 to 2 for count, 4 for max, 5 for min etc. Other function_num in help.

Make sure that the range is the full possible range of values to be sub-totaled and therefore any new data that is filtered will be correct (after running macro or whatever)