=SUMPRODUCT(D5:D1009="",C5:C1009,B5:B1009)
Just learnt an easy way to do conditional SumProduct
^{1} calculations in Google Sheets^{2}.
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,
SumProduct
is 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”