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

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!

• I have discovered a working function!! Now, is there any way to reduce this mess so I can easily go from G-Z and beyond? =SUM(If((G4-G3)>0,G4-G3,0),if((H4-H3)>0,H4-H3,0),if((I4-I3)>0,I4-I3,0)) Dec 10, 2015 at 22:41

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.

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.