Summing Between Blanks

Gakinmiff

New Member
Joined
Oct 20, 2006
Messages
6
In column L, I want to create a function where if the cell next to it in column K is >0, then it is 0, but if it is =0, then it adds all the cells going up to the next cell which =0 So since K10 and K4 =0, L10 is the sum of K5:K9. But then once it finds the next cell=0 I don't want it does to look further.

A string of IF statements only goes so far (I think 6 nested statements). Any thoughts?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
L2:

=MATCH(TRUE,K2:K100<>0,0)

L3:

=MATCH(TRUE,INDEX(K2:K100,L2):K100=0,0)+L2-1

L4:

=SUM(INDEX(K2:K100,L2):INDEX(K2:K100,L3-1))

Every formula above needs to be confirmed with cntrol+shift+enter, not just with enter.

L4 comes to house the desired result.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,179
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top