0

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!

1
  • 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

2 Answers 2

0

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.

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .