Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel2003 - Automatically sum a variable number of rows
David,
You are right ... with True or False instead of If ... Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel2003 - Automatically sum a variable number of rows
Andrew,
Glad it fixed your problem ... Thanks for the feedback |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy cells to variable number of rows | Excel Discussion (Misc queries) | |||
copy down with variable number of rows | Excel Discussion (Misc queries) | |||
Linking to a Variable Number of Rows - XP/07 | Excel Worksheet Functions | |||
Insert Variable Number of Rows; With Loop | Excel Worksheet Functions | |||
How do I assign a text value to a variable number of rows? | Excel Worksheet Functions |