multiple condition if formula google sheet

0
votes

I have a sheet with this value:

 VALUE1 QTY1 VALUE2 QTY2 VALUE3 QTY3
    10     2    12     -2   13     3

I would like sum the product of values and quantity only if the quantity is positive but can't image of a way to do it using a single formula in google sheet.

Could you please help me?

Thanks

2
google-sheetsgoogle-sheets-formula
What is the range of this data? Will it always be ONLY and EXACTLY three pairs as you show in your post, or might it be 2 ... or 10? Will you be trying to do this for only ONE row ... or for many rows? If many rows, will each row contain the same number of pairs? As you can see, there are many things still unknown. – Erik Tyler

2 Answers

1
votes

You could do it with two offset ranges (ie the second range starts one cell to the right of the first range):

=ArrayFormula(sum(isodd(column(A2:Y2))*A2:Y2*iseven(column(B2:Z2))*B2:Z2*(B2:Z2>0)))

Or a bit more dynamic to allow for adding more columns:

=ArrayFormula(sum(isodd(column(A2:2))*A2:2*{iseven(column(B2:2))*B2:2*(B2:2>0),0}))

enter image description here

0
votes

I took the liberty of rearranging your data so you would only need a single formula even if you add more values to the table.

enter image description here

You can use SUMPRODUCT for this:

=SUMPRODUCT(A2:A,IF(B2:B>0,B2:B,0))