Updating formula, so table only reads filtered numbers.

[Livin404]

Greetings, I have the following formula and many others like it to get totals.

=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.

 

[jasonb75]

Try this highly inefficient 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),

=SUBTOTAL(109,D16)

Then get your result with

=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.