Excel2003 - Automatically sum a variable number of rows
 

How do I automatically sum a variable number of rows between two delimiters?

I get a monthly spreadsheet from an accounting program and add formulas to
it to make it usable for others.

The sheet contains info about projects; each project is a set of a variable
number of rows. There is always a non-bold "F" at the start of the set and a
bold "F" at the end. The end of the set I want to sum is always in the same
position relative to where the sum function is, but I can't work out how to
find the other end, in a formula. This has to be formula driven as there are
too many sets to do it manually.

Example:
A B C
Job1 JobName F
Line1 500
Line2 1000
Line3 1500
Line(n) 1000
Job1 JobName F
(SUM FUNCTION)

Excel2003 - Automatically sum a variable number of rows
 

Hi Andrew,

Copy following formula in cell D1 ... and all the way down ...
=SUM(C1:INDIRECT(ADDRESS(SUMPRODUCT(MAX((ROW($C$1: C1))*($C$1:C1="F"))),
3,4)))*IF(OFFSET(C1,1,0)="F",1,0)

HTH

Excel2003 - Automatically sum a variable number of rows
 

You can simplify that a bit.

....)))*IF(OFFSET(C1,1,0)="F",1,0)
can presumably be replaced by
....)))*(OFFSET(C1,1,0)="F")
--
David Biddulph

"Carim" wrote in message
...
Hi Andrew,

Copy following formula in cell D1 ... and all the way down ...
=SUM(C1:INDIRECT(ADDRESS(SUMPRODUCT(MAX((ROW($C$1: C1))*($C$1:C1="F"))),
3,4)))*IF(OFFSET(C1,1,0)="F",1,0)

HTH

Excel2003 - Automatically sum a variable number of rows
 

Carim

Many Thanks - that seems to do it - I'll have to study the functions you
have used to understand how they operate. But for now I'll just accept that
they work.

Andrew

"Carim" wrote:

Hi Andrew,

Copy following formula in cell D1 ... and all the way down ...
=SUM(C1:INDIRECT(ADDRESS(SUMPRODUCT(MAX((ROW($C$1: C1))*($C$1:C1="F"))),
3,4)))*IF(OFFSET(C1,1,0)="F",1,0)

HTH

Excel2003 - Automatically sum a variable number of rows
 

David,

You are right ... with True or False instead of If ...

Thanks

Excel2003 - Automatically sum a variable number of rows
 

Andrew,

Glad it fixed your problem ...

Thanks for the feedback