# Subtotals Filter when Table is Filtered

#### scott11

##### New Member
Hello,

I have two tables in my spreadsheet. Table 1 allows me to input different orders, companies, and totals. Table 2, looks for key words under Order Type and adds up all the totals that match that key word (as shown in the below example). However, when I filter Table one to include only the totals for Company A, I want the totals in Table 2 to reflect only those totals and not the totals from everything in Table 1. Currently, if I filter out Company B from Table 1 I still show a total of \$55 in Table 2, though the new total should equal \$40.

My formula for Materials in Table 2 is as follows: =SUMIF(\$H\$13:\$H\$32,"Materials",\$V\$13:\$V\$32), with column H being Order Type and column V being Subtotal.

I can add a final row to the bottom of Table 1 and input a SUM function that will return a sum of only those numbers shown during a filter, but I'm hoping I can show that in Table 2 instead. I've include what it would look like in the second set of pictures below.

Table 1

Table 2

#### Attachments

• 1609441400868.png
7.1 KB · Views: 0

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Fluff

##### MrExcel MVP, Moderator
How about
Excel Formula:
``=SUMPRODUCT((\$H\$13:\$H\$32="Materials")*(SUBTOTAL(3,OFFSET(\$H\$13:\$H\$32,ROW(\$H\$13:\$H\$32)-MIN(ROW(\$H\$13:\$H\$32)),,1))),\$V\$13:\$V\$32)``

#### scott11

##### New Member
If table 1 is a structured table, the simplest way would be to add a total row & link to that.
Otherwise you would need something like
Excel Formula:
``=SUMPRODUCT((\$H\$13:\$H\$32="Materials")*(SUBTOTAL(3,OFFSET(\$H\$13:\$H\$32,ROW(\$H\$13:\$H\$32)-MIN(ROW(\$H\$13:\$H\$32)),,1))),\$V\$13:\$V\$32)``

Thanks Fluff, I'm heading out for the holiday so I will have to look at what you provided on Monday. Happy New Years!

#### Fluff

##### MrExcel MVP, Moderator
Ok, Happy new year to you.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
1
Views
44
Replies
1
Views
90
Replies
1
Views
169
Replies
4
Views
221
Replies
12
Views
208

Threads
1,127,652
Messages
5,626,091
Members
416,161
Latest member
David1966Lewis

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back