Sum If Formula

[jthompson]

I am trying to have the cell in column I look at column G. If that cell has a 0 I want to sum the number above up stopping at the next zero above. For instance cell G 7 has a zero. I7 needs to sum G2:G6. I have this somewhat working but the sum is appearing in cell I1. The sum is i7 is actually summing G8:G9 this is correct but that sum needs to be in I10. Basically its putting the sum above rather than below.

=IF(G7=0,SUM(INDIRECT("G"&ROW()+1&":G"&IFERROR(MATCH(0,INDIRECT("G"&ROW()+1&":G"&ROW()+100),0)+ROW()-1,ROW()+100))),"0")

 

[awoohaw]

@jthompson
Would you be kind enough to post a mini worksheet using the xl2bb add in (Link below)
And also, please tell us what version of excel you use, and update your profile so the version you use shows up on your thread button.

Solutions may differ from version to version. And a better solution may be available by seeing what your data is like and getting an idea of the objection.
I am loathe to use INDIRECT or OFFSET in any bulk formula as they can affect processing time substantially.

If for some reason you are unable to use xl2bb, please paste a TABLE and not an image. Images require that the forum completely recreate your scenario and that WILL take time and can possibly have errors by typos. If you must post a table please identify columns and rows, give formulas in the text (and identify the cell(s)). And if at all possible expected values from the calculations.

Thanks in advance.

 

[Peter_SSs]

Thanks for updating your version details.

See if this is what you want.

23 05 05.xlsm
	G	H	I
1
2	2
3	3
4	5
5	1
6	4
7	0		15
8	3
9	6
10	0		9
jthompson

Cell Formulas
Range	Formula
I2:I10	I2	=IF(G2=0,SUM(G$2:G2)-SUM(I$1:I1),"")

 

[jthompson]

This is perfect !! Thanks so much for your help!!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[desertgal]

@jthompson , thanks for this question. I've needed something like this for quite a while and never sat down to figure it out.
Glad @Peter_SSs answered before I did. The simplicity is lovely thus far better than what I came up with.


I made a complicated formula starting with yours and using the Aggregate function to sum numbers back to the immediate prior zero. (Your formula worked summing from the top, as it found the first occurrence of zero below the current row. Couldn't use the same formula going up as it would always look to the first zero in the column)

=IF(G7=0, SUM(INDIRECT("G"&ROW()-1&":G"&IFERROR(MATCH(0,INDIRECT("G"&ROW()-1&":G"&(AGGREGATE(15,6,ROW($1:7)/($G$1:$G7=0),COUNTIF($G$1:$G7,0)))),0)+AGGREGATE(15,6,ROW($1:7)/($G$1:$G7=0),COUNTIF($G$1:$G7,0)),AGGREGATE(15,6,ROW($1:7)/($G$1:$G7=0),COUNTIF($G$1:$G7,0))))) ,"")

 

[Peter_SSs]

I made a complicated formula ..

You certainly did!
Welcome to the MrExcel board!

If you did want to actually look upwards for the previous zero and just sum that range rather than the way I did it above, then you could still avoid such a complicated formula (and multiple uses of the volatile function INDIRECT) as follows:

23 05 05.xlsm
	G	H	I
1
2	2
3	0		2
4	5
5	1
6	4
7	0		10
8	3
9	6
10	0		9
jthompson (2)

Cell Formulas
Range	Formula
I2:I10	I2	=IF(G2=0,SUM(XLOOKUP(0,G$1:G1,G$1:G1,G$1,,-1):G1),"")

 

[desertgal]

@Peter_SSs , once again, Wow!

Range	Formula
Cell Formulas
I2:I10	I2	=IF(G2=0,SUM(XLOOKUP(0,G$1:G1,G$1:G1,G$1,,-1):G1),"")

That's the one I'll be using! I'll sometimes use formula in a column that has other numbers, so the simple subtraction method won't work in all cases for me.
Agreed about using the Indirect formula -- it's unweildy and hard to troubleshoot. Using a butter knife when I don't have a screwdriver handy.