How To Get Sum Of Filtered Data In Excel

A mutual question I become asked when I'm teaching people how to filter information is "how do I sum just the visible rows when filtering information in Excel"?

It's very mutual for people to try creating a SUM full at the lesser of a listing and then filter the data hoping that the total volition modify to reflect the SUM of the information displayed. Even so, the total doesn't update. It withal shows the SUM of the entire list.

You can definitely use the AutoSum push button to instantly create a full that volition update as you filter your data. Here's how you practise information technology.

Sum only the visible rows

The commencement thing you lot will need to do is utilize Filter to your data and then be sure to have the data filtered Before trying to SUM the range. If you filter later applying the SUM part you will even so run across the full including the data hidden by the filter.

1. Select the data to exist filtered and so on the Information tab click Filter. Use the filter arrows to filter the data. Do this prior to inserting the SUM function.

count filtered rows in excel

2. Now select the jail cell in which you desire the SUM to be shown.

excel count filtered rows

three. From the Domicile tab click the AutoSumbutton, or press ALT + = which is the keyboard shortcut for SUM.

filter formula excel

4. Select over the filtered range that y'all want to SUM and then printing Enter.

filter formula in excel

5. A SUBTOTAL part will exist created for the selected range. The SUBTOTAL function will update the total to display the SUM of only the visible cells.

filter data in excel

In the example higher up the SUBTOTAL function has been applied to full the Total Sales list.

Be sure to cheque that the range in the SUBTOTAL function includes all cells required.

The function number '9' is important as Excel uses this to determine the function used to create the subtotal result. The number 9 will create a SUM subtotal at each alter in the data.

Use the tabular array below to select the type of subtotal required.

FUNCTION NUMBER	FUNCTION Name
1	AVERAGE
ii	COUNT
3	COUNTA
4	MAX
5	MIN
9	SUM

Exclude information in subconscious rows

If the range includes hidden rows these will be included in the subtotal.

To exclude data in hidden rows employ the role number plus 100, due east.g. 109 volition produce a SUM excluding subconscious rows.

When your full returns an mistake

If your total returns an error y'all may need to fix something inside the data. Cheque our my post 5 Reasons why your Excel filter may not be working.

Using Excel Tables to subtotal your data

Excel Tables provide another brilliant style to subtotal your data. To larn more about them check out my post on how to use Excel Tables.

Was this blog helpful? Let us know in the Comments below.

If you lot enjoyed this mail cheque out the related posts below.