How do I sum a row of cells under the condition that each cell is less than the

Question

How do I sum a row of cells under the condition that each cell is less than the one above it?

I have two rows of data, like this

5 3 7 5 3 1 6 5 8 6 4 4 3

4 6 3 1 7 1 7 3 6 4 3 9 6

I want to sum the values in the bottom row, but only if the values in that row are greater than the value in the cell directly above it. So in this case, I want to sum 6 + 7 + 7 + 9 = 29

Now, I also want to know how to sum the difference of those two cells, but with the same condition that I only want to sum the difference if the value in the bottom cell is greater than the value in the top cell. So in this example, I want to sum (6-3) + (7-3) + (7-6) + (9-4) = 13.

Thanks !!

Explanation / Answer

Hi..

Enter the below formulas as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

=SUM(IF(A2:M2>A1:M1,A2:M2))

=SUM(IF(A2:M2>A1:M1,A2:M2-A1:M1))

Note: Answers would be 35 and 16 as last column has 6 which is greater than 3.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.