=SUMPRODUCT(D5:D1009="",C5:C1009,B5:B1009)
Just learnt an easy way to do conditional SumProduct1 calculations in Google Sheets2.
In above example, I wanted the SumProduct of values in columns B and C, when the respective cell in column D was empty.
The solution is to simply introduce the conditional column as another input, with the condition stated in the input.
Sheets does the SumProduct calculations, with each true as 1, and false as 0, thus making it a Conditional SumProduct :)
-
In the remote case you didn’t know it already,
SumProductis a spreadsheet function that multiplies values in similarly indexed cells (so 1st cell of range 1 with 1st cell of range 2, etc), and then sums up all the products of those multiplications. ↩ - I’m ashamed that it took me so long to discover this. I’ve been using Sheets as my primary, if not only, spreadsheet software for over 7 years! Ashamed! ↩
One thought on “TIL: Conditional sum product in Google Sheets”