TIL: Conditional sum product in Google Sheets
=SUMPRODUCT(D5:D1009="",C5:C1009,B5:B1009)
Just learnt an easy way to do conditional SumProduct1calculations in Google Sheets2.
In above example, I wanted theSumProductofvalues in columns Band C, when the respective cell in column D was empty.
The solution is to simply introducethe conditional column as another input, with the condition stated in the input.
Sheets does the SumProduct calculations, with each trueas 1, and falseas 0, thus making it a Conditional SumProduct :)
- In the remote case you didnt know it already, SumProductis a spreadsheet functionthat multiplies values insimilarly 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.
- Im ashamed that it took me so long to discover this. Ive been using Sheets as my primary, if not only, spreadsheet software for over 7years! Ashamed!
Like this:
Like Loading...