Updating formula, so table only reads filtered numbers.

Livin404

Active Member
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.

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
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)``
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.

Replies
4
Views
215
Replies
5
Views
331
Replies
3
Views
79
Replies
1
Views
303
Replies
0
Views
192

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.

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

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