Sum of variable number of rows

**Aussie-Clive** · 08-07-2009, 12:04 AM

G'day All,

I reckon this should be easier than I'm finding it, but I just can't seem to find the right combination of functions to solve this.

In column A I will have an entry every 5 or 6 or 7 rows with blanks rows between.
In Column B starting at every row corresponding with an entry in Column A there will be a short column of figures could be 1 2 3 or four or more of them. (the're will always be a blank between the last number in a group in B before the next entry in A)
In column C starting at every Row corresponding to an entry in column A I want the sum of the group of figures in Column B.

But here's the rub.... The most important thing is that I can use the same formula all the way down column C. So click the one formula at the top and drag it all the way down 1000 rows.

I know this is easy for those with a bigger brain than mine.... but I'm stumped!

**JBeaucaire** · 08-07-2009, 12:57 AM

I'm trying to stay away from ARRAY formulas, so will this work for you? It puts the total on the last row of each valid entry before the blanks. Put this in C2 and copy down:

=IF(AND(B1<>"",B2=""),SUM($B$1:B1)-SUM($C$1:C1),"")

**Aussie-Clive** · 08-07-2009, 01:54 AM

Thats great, that's why your rocket goes up and mine crashes & burns.

I want to get the result on the same row as the entry in column A for easy referencing, but the solution itself is logical, elegant and simple - just what I wanted.

Thanks very much.

**DonkeyOte** · 08-07-2009, 02:17 AM

If you wanted the value in the row in which header appears in Col A then:

C2:
=IF($A2<>"",SUM($B2:INDEX($B2:$B$100,MATCH(TRUE,INDEX($B2:$B$100="",0),0))),"")
copied down

(alter ranges to suit)