Search code examples
excelgoogle-sheetsgoogle-sheets-formulapercentagesumproduct

Best way to use SUMPRODUCT only on specific rows?

I am trying to do SUMPRODUCT in Google Sheets but in a more complicated situation.

This is what I am doing now, and it works just fine, the thing is - I want to be able to use that exact function on specific rows, so I will be able to sum only some of my expanses, for example.

    A       B       C       D
1           Price   Tax     Cashback
2           100     1.09    0.95
3           80      1       1
4           50      1.09    0.95
5           30      1.09    0.95
6   Total

If I want to sum everything I simply do =SUMPRODUCT(B:B, 1+C:C, 1-D:D) and it works great.

I want to do something like =SUMPRODUCT({B1,B5}, 1+{C1,C5}, 1-{D1,D5}) but sadly it doesn't work.

Any help would be appreciated, thank you

Solution

  • try:

    =SUMPRODUCT({B1;B5}, 1+{C1;C5}, 1-{D1;D5})