# How to SUM Only Visible (or Filtered) Rows Using SUBTOTAL

## Creating a Data Table

This step isn’t necessary to use SUBTOTAL, but it is a good way to set up filtering on a data set. Let’s create one. First, select the table, including the header row that has the data labels.

In the Insert menu tab under the Tables section, click on the Table button to convert the data cells into a table.

In the Create Table dialog that appears, make sure that My table has headers is checked and click OK.

Your data will now be structured in a table, which makes it easy to reference rows, columns, and perform filtering and sorting actions.

## Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

## 12 thoughts on “How to SUM Only Visible (or Filtered) Rows Using SUBTOTAL”

1. Martin says:

What if I wanted to show a rank of the filtered data?

2. Apostolos55 says:

ok, try adding a dummy column with a subtotal, example in row 10: =subtotal(103;a10)

now you have another criteria depending on the filter, so all multi-criteria formulas will work as well, far beyond the ones provided with subtotal!

enjoy…

1. Woozie Boozie says:

hey it works brilliantly what you told, but if I drag the same formula for the entire Column, Excel doesn’t accept that.

1. Apostolos55 says:

It is like any other formula, copy the couple first ones so 103 doesn’t change, then pick them all and drag. It really wants no special treatment.

3. sam says:

Hi There,

I’m trying to add a total from each separate tab on my spreadsheet to give a summary of the totals on a final tab, if for some reason one of the tabs is removed / deleted it throws out the formula on the final sheet as the tab its looking for no longer exists. How do I tell Excel to ignore deleted tabs?

Any help would be appreciated, I am afraid I’m a novice on excel and just getting to grips with the basics it seems!

Thanks

Sam

1. Apostolos55 says:

hi, although out of topic I’ll give a simple and effective answer:
rap individual sums in IfError()
write: =sum(IFERROR(Sheet1!B11,0),IFERROR(Sheet2!B11,0))
and so on…

4. Amy says:

Is it possible to have a separate tab that would show the running total for each region? So if you added something onto East it would automatically add it on and update the other tab? Or would you need to have totals on that page for it to work?

Hi,

I want to show the filtered value in a separate cell.. say from the above example if we filter data on “WEST”, i want “WEST” to be shown in separate cell (dynamically, that cell should show filtered data what is filtered). Glad if this is possible thru formulas (not VBA)

6. Ravi says:

Excellent Example ….Solved my problem

7. Erick says:

Hello there..kindly help me on this: I want to grade only 7 subjects out of 11. 4 are core the rest are optional and a student must therefore till they’re a minimum of 7. I will be glad if you reply to my email too.

8. Thapelo says:

I want to use the AVERAGEIF, SUMIF AND COUNTIF functions but want them to only apply to the visible data on a filtered table. How do i do this?

9. shivam says:

if i want to skip “William Foster” in above table and subtotal automatically skip “William Foster” and add “Robert” to do the sum of last 4 managers…..
is it possible??