MrExcel Publishing
Your One Stop for Excel Tips & Solutions

AutoFilter (SUBTOTAL Formula)


Posted by Jo on January 08, 2002 6:51 AM

I've got 2 columns in question. One is for Weights with a total at the bottom. The other is the Load column. I've got the AutoFilter set on the Load column. I've put the formual =SUBTOTAL(9,G15:G100)
at the end of the weight column. However, it's not working like I thought. I thought it was supposed to show the weight for only what was sorted. When I sort, nothing shows for the total weight. What am I doing wrong?


Posted by Scott on January 08, 2002 7:10 AM

The Subtotal function should work like you say. Couple of questions: Does your data in column G go from row 15 through 100? When you filter the Load column, does it hide the subtotal for the weight column?

Posted by Jo on January 08, 2002 7:15 AM

The weight does go from G15 thru G100 and when I filter it does hide the subtotal.. Can you help with this?

Posted by Scott on January 08, 2002 7:16 AM

I've found that when using Auto Filter, it's easier to put the Subtotals up top, above the data that you are filtering. That way they will not get filtered out. If they need to go at the bottom, you may want to try and skip a row or two between your data and the subtotal.

Posted by Mark W. on January 08, 2002 7:18 AM

Enter your SUBTOTAL formula in cell G102 leaving
row 101 completely blank. You may want to remove
your AutoFilter and then reapply it with any cell
between G15 and G100 selected.