SUM the cells between two cells with zeros (from formula) in a column, multiple occurances

[C.L.Garthwaite]

I have a column of numbers (results from formulae) for which I would like to return SUMs for the values between randomly spaced zero values.
If this is not possible, I could have null values (#VALUE!) rather than zeroes. (But I'd like to hide any #VALUE! from displaying/printing)

This is very similar to the thread: Macro to SUM the cells between two blank cells in a column, multiple occurances

link: http://www.excelforum.com/excel-prog...ccurances.html

example:

100
23
2 125
0
47
52 99
0
2347.5
28 2375.5
0

Thanks!

[Bob Phillips]

Try this

=IF(Q14<>0,SUM(R13:R13,Q14),0)

[C.L.Garthwaite]

Hi Bob, Thanks..! Close, but I'd also like it to leave the intermediate subtotals blank. If I don't see any replies that can do that, I may end up using it. I do like the fact that your solution is a formula rather than a macro, but for this instance, I'm thinking it will very likely require a macro. Best Regards, Carl

[Bob Phillips]

No, just adjust to

=IF(Q14<>0,SUM(R13,Q14),"")

[C.L.Garthwaite]

Hi Bob, Hmmm... That ignores text well, (apparently in this instance Excel does return a zero for text) and also gives me a very nice blank...
But, I can make the zeros not visible (and won't print) from TOOLS, OPTIONS, & uncheck Zero Values.
However, it still doesn't give me a blank (or a zero which I could hide) before the totals... I used to be great at HP basic... that had IF, THEN, OR, ELSE, and ELSEIF, but excel doesn't which throws me off my game here... Maybe a nested IF... (but I usually get bad results from trying that...) But it does give me a few ideas... I'll try a few more things... Thanks again!!!

[Bob Phillips]

I'm missing something, which cells are not showing as you would want, and in what way?

[C.L.Garthwaite]

Hi Bob, Thank you so much for your time and effort! OK... I hope this makes sense...
Q13 is text (should evaluate to zero) Q14 is 12.4, Q15 is 2.3 and Q16 is zero.
I need to check Q13 through Qn to check for valid numbers until it finds zeros at both ends (Q16 in this case) and add all the valid numbers
(Q14 to Q15) in R15 (Rn), but leave out the valid number for Q14 from R14. Plain as mud eh? The first formula you gave worked perfect, except for populating R14 (and since I'm using the formula for every line in column R) and R17) I could do this in hp basic, which is why it's making feel like a dummy trying to do it in Excel. Anyhow thanks! you're a saint! I'm outta here in 15, so please don't spend too much time on it, unless the puzzle has you hooked!

[C.L.Garthwaite]

Oh BTW... stanleydgromjr had a near solution in the post: http://www.excelforum.com/excel-prog...ccurances.html except it didn't like to pull the numbers from formulas...

[stanleydgromjr]

C.L.Garthwaite,

Thanks for the Private Message.

The majority of the cells in your attached workbook can NOT be clicked into??????

Please remove any protection from the workbook, and, repost it.

[Bob Phillips]

Why don't you post the workbook with the values in column R that you want to get, I'm sure it will be simple then.

[jindon]

Try this

Please Login or Register  to view this content.

[C.L.Garthwaite]

Hi Jindon, Perfect! Exactly what I needed! Thanks a million! Best regards, Carl

[C.L.Garthwaite]

I want to thank Everyone who looked at this post and gave suggestions for their time and effort.
PM me if any of you need any AutoCad 2000-2008 help!

[stanleydgromjr]

C.L.Garthwaite,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.