Subtotals Filter when Table is Filtered

scott11

New Member
Joined
Sep 13, 2016
Messages
28
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
1609441387034.png


Table 2
1609441774307.png



1609441825865.png

1609441837172.png
 

Attachments

  • 1609441400868.png
    1609441400868.png
    7.1 KB · Views: 0

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

scott11

New Member
Joined
Sep 13, 2016
Messages
28
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
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
Ok, Happy new year to you. 🥳
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

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

Which adblocker are you using?

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
Top