Excel, possible array? Not sure how to calculate conditional steps

Question

I have two rows of numbers, 1 and 2, they go from G-Z. I want to calculate the following:

(G2-G1, if greater than zero) add to...
(H2-H1, if greater than zero) add to...
(I2-I1, if greater than zero) add to...

All the way to z.

I've been fiddling with sumif and I don't know if I'm on the right track but I'm not getting the right result.

Help!

Answer 1

You can wrap that in an array formula like so:

=SUM(IF(G2:Z2 - G1:Z1 > 0, G2:Z2 - G1:Z1, 0))

Then to enter it as an array formula, hold SHIFT and CTRL while pressing ENTER.

If you did it right, it will be surrounded with curly brackets when you select the cell:

{=SUM(IF(G2:Z2 - G1:Z1 > 0, G2:Z2 - G1:Z1, 0))}

Note that you should not type the curly brackets.

---

As a side note, I would normally recommend using the MAX function to test for 0 in cases like this. It simplifies the formula from this:

=IF(G2:Z2 - G1:Z1 > 0, G2:Z2 - G1:Z1, 0)

To this:

=MAX(G2:Z2 - G1:Z1, 0)

However, this simply will not work in an array formula because the MAX function aggregates the array similar to SUM, meaning that in this case, it first finds the max of G2:Z2 - G1:Z1 and then finds the max between that max and zero, which is definitely not what we were attempting to do.

Answer 2

Isnt that just:

=SUM((G2:Z2-G1:Z1)*(G2:Z2>G1:Z1))

This is an array formula and must be confirmed with Ctrl+Shift+Enter.