Updating formula, so table only reads filtered numbers.

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Greetings, I have the following formula and many others like it to get totals.
Excel Formula:
=SUMPRODUCT(D16:D100001,--(C16:C100001="Originating"))/2000
However, I will be using a filter so I will need to have some sort of "Subtotal" in the equation. The formulas do work as do the filter buttons you see above the totals page. I'm hoping to tweak each formula to show only the filtered results in the table above the database. Also I will need the totals to revert back to the grand totals once the filter is turned off. I wish I could use the pivot/slicer process but my supervisor wants be to stay away from it. I will create one once people get use to this process. Thank you.

Capture.JPG
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,453
Office Version
  1. 365
Platform
  1. Windows
Try this highly inefficient formula
Excel Formula:
=SUMPRODUCT(D16:D100001,(C16:C100001="Originating")*SUBTOTAL(103,OFFSET(C16,ROW(C16:C100001)-ROW(C16),0,1,1)))/2000
The more efficient way would be to add an extra column to the data table with a subtotal for each row, for example, in L16 and filled down to the end of the data (use first available column if L is already in use),
Excel Formula:
=SUBTOTAL(109,D16)
Then get your result with
Excel Formula:
=SUMPRODUCT(L16:L100001,--(C16:C100001="Originating"))/2000
but my supervisor wants be to stay away from it
Hopefully, once your supervisor realises how impractical it is to do what is required with a formula they will get a grip on reality and let you use the slicer.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,584
Messages
5,625,641
Members
416,124
Latest member
DeMoNloK

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