CODEWITHSUNDEEP
excel
Matt A
Matt A

Reputation: 3

Calculate a Subtotal of a Sumproduct

I believe I am asking the same questions as was asked here, but am unable to comment and cannot make the accepted answer from @barry houdini work.

My situation is very similar, as I need a subtotal of a sumproduct, so that when I filter my results, I get an updated total representative of the displayed data.

More specifically, I want the sumproduct of qty and price, but if there is a purchase date, I want to filter to show only Blank cells in that column to find the outstanding purchasing total.

Quantity             Price              Purchase Date
1                    150                3/1/2017
2                    500                           
1                    50                 3/15/2017
1                    100

Is there an easy way to do this in one formula without needing extra columns for totals? Or should I come at it a different way?

Upvotes: 0

Views: 1708

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

With your data setup the formula would be:

=SUMPRODUCT(A2:A5,SUBTOTAL(9,OFFSET(B2:B5,ROW(B2:B5)-MIN(ROW(B2:B5)),0,1)))

enter image description here

But if you always want the non date sumproduct then you do not need the subtotal or the filter. Just use:

=SUMPRODUCT(($C$2:$C$5="")*$A$2:$A$5,$B$2:$B$5)

enter image description here

Upvotes: 1

Related Questions