I have a spreadsheet that has twelve sections (ie one per month of financial
year). In each section there is data pasted in from another worksheet and
below that data is a summary table that uses various formulas to sum the
above data based on variables in the data. So, in the below formula, in F29 I
manually type in the starting row for the data, G29 has the ending row
manually typed in, D31 is the variable that states which data to sum and
Column E has the data that D31 is looking for.

=SUMIF(INDEX($E:$E,F29):INDEX($E:$E,G29),D31,INDEX ($I:$I,F29):INDEX($I:$I,G29))

The formula works very well, HOWEVER some months have much more data than
others so my bookkeeper has to manually change the row numbers (defined for
Month X in F29 and G29 above). Every now and then she forgets to change the
row numbers or has a typo which means I have to check this spreadsheet every
month.

My question is, what is the formula that I can put in F29 and G29 (and the
corresponding cells for each other month) that will always return the
starting row and ending row of month X even if rows in between are added or
deleted or if rows in a prior month are changed.

In other words say April's data starts on row 121 and ends on row 149. So I
manually type in 121 and 149 in the appropriate "F and G 29". Later, say
March gets new data and I have to insert rows for March. Now April starts on
row 126 and ends on 155, but the summary box still shows 121 and 149 until I
change it manually. How do I do it automatically?

Two ideas. The first would be to use the MATCH function to find a certain
date within column F, giving you the row number you need.
e.g.,
=MATCH(F29,I:I,0)
Where F29 contains your start date.

or, rearrange your formula and simplyify it with SUMPRODUCT. Assuming your
dates are in column A, lets say your start and end dates are in F29 and G29
respectively.

=SUMPRODUCT(--($A$2:$A$1000=F29),--($A$2:$A$1000<=G29),--($E$2:$E$1000=D31),$I$2:$I$1000)

Note that with SUMPRODUCT, all array sizes must be equal, and you can't call
out the entire column unless using XL 2007.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MurrayBarn" wrote:

I have a spreadsheet that has twelve sections (ie one per month of financial
year). In each section there is data pasted in from another worksheet and
below that data is a summary table that uses various formulas to sum the
above data based on variables in the data. So, in the below formula, in F29 I
manually type in the starting row for the data, G29 has the ending row
manually typed in, D31 is the variable that states which data to sum and
Column E has the data that D31 is looking for.

=SUMIF(INDEX($E:$E,F29):INDEX($E:$E,G29),D31,INDEX ($I:$I,F29):INDEX($I:$I,G29))

The formula works very well, HOWEVER some months have much more data than
others so my bookkeeper has to manually change the row numbers (defined for
Month X in F29 and G29 above). Every now and then she forgets to change the
row numbers or has a typo which means I have to check this spreadsheet every
month.

My question is, what is the formula that I can put in F29 and G29 (and the
corresponding cells for each other month) that will always return the
starting row and ending row of month X even if rows in between are added or
deleted or if rows in a prior month are changed.

In other words say April's data starts on row 121 and ends on row 149. So I
manually type in 121 and 149 in the appropriate "F and G 29". Later, say
March gets new data and I have to insert rows for March. Now April starts on
row 126 and ends on 155, but the summary box still shows 121 and 149 until I
change it manually. How do I do it automatically?

Dont worry about helping - have found the solution:

=cell("row",f29)

Regards



"MurrayBarn" wrote:

I have a spreadsheet that has twelve sections (ie one per month of financial
year). In each section there is data pasted in from another worksheet and
below that data is a summary table that uses various formulas to sum the
above data based on variables in the data. So, in the below formula, in F29 I
manually type in the starting row for the data, G29 has the ending row
manually typed in, D31 is the variable that states which data to sum and
Column E has the data that D31 is looking for.

=SUMIF(INDEX($E:$E,F29):INDEX($E:$E,G29),D31,INDEX ($I:$I,F29):INDEX($I:$I,G29))

The formula works very well, HOWEVER some months have much more data than
others so my bookkeeper has to manually change the row numbers (defined for
Month X in F29 and G29 above). Every now and then she forgets to change the
row numbers or has a typo which means I have to check this spreadsheet every
month.

My question is, what is the formula that I can put in F29 and G29 (and the
corresponding cells for each other month) that will always return the
starting row and ending row of month X even if rows in between are added or
deleted or if rows in a prior month are changed.

In other words say April's data starts on row 121 and ends on row 149. So I
manually type in 121 and 149 in the appropriate "F and G 29". Later, say
March gets new data and I have to insert rows for March. Now April starts on
row 126 and ends on 155, but the summary box still shows 121 and 149 until I
change it manually. How do I do it automatically?

You might want to recheck that. That formula will always return a value of
29, which doesn't seem to fit with what you described in your original
problem.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MurrayBarn" wrote:

Dont worry about helping - have found the solution:

=cell("row",f29)

Regards



"MurrayBarn" wrote:

I have a spreadsheet that has twelve sections (ie one per month of financial
year). In each section there is data pasted in from another worksheet and
below that data is a summary table that uses various formulas to sum the
above data based on variables in the data. So, in the below formula, in F29 I
manually type in the starting row for the data, G29 has the ending row
manually typed in, D31 is the variable that states which data to sum and
Column E has the data that D31 is looking for.

=SUMIF(INDEX($E:$E,F29):INDEX($E:$E,G29),D31,INDEX ($I:$I,F29):INDEX($I:$I,G29))

The formula works very well, HOWEVER some months have much more data than
others so my bookkeeper has to manually change the row numbers (defined for
Month X in F29 and G29 above). Every now and then she forgets to change the
row numbers or has a typo which means I have to check this spreadsheet every
month.

My question is, what is the formula that I can put in F29 and G29 (and the
corresponding cells for each other month) that will always return the
starting row and ending row of month X even if rows in between are added or
deleted or if rows in a prior month are changed.

In other words say April's data starts on row 121 and ends on row 149. So I
manually type in 121 and 149 in the appropriate "F and G 29". Later, say
March gets new data and I have to insert rows for March. Now April starts on
row 126 and ends on 155, but the summary box still shows 121 and 149 until I
change it manually. How do I do it automatically?

Thanks Luke

I have checked it and it seems to stick to the row I defined initially so I
can insert and delete rows to my hearts content and the block of data for
month X is now pretty much absolute

Cheers

"Luke M" wrote:

You might want to recheck that. That formula will always return a value of
29, which doesn't seem to fit with what you described in your original
problem.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MurrayBarn" wrote:

Dont worry about helping - have found the solution:

=cell("row",f29)

Regards



"MurrayBarn" wrote:

I have a spreadsheet that has twelve sections (ie one per month of financial
year). In each section there is data pasted in from another worksheet and
below that data is a summary table that uses various formulas to sum the
above data based on variables in the data. So, in the below formula, in F29 I
manually type in the starting row for the data, G29 has the ending row
manually typed in, D31 is the variable that states which data to sum and
Column E has the data that D31 is looking for.

=SUMIF(INDEX($E:$E,F29):INDEX($E:$E,G29),D31,INDEX ($I:$I,F29):INDEX($I:$I,G29))

The formula works very well, HOWEVER some months have much more data than
others so my bookkeeper has to manually change the row numbers (defined for
Month X in F29 and G29 above). Every now and then she forgets to change the
row numbers or has a typo which means I have to check this spreadsheet every
month.

My question is, what is the formula that I can put in F29 and G29 (and the
corresponding cells for each other month) that will always return the
starting row and ending row of month X even if rows in between are added or
deleted or if rows in a prior month are changed.

In other words say April's data starts on row 121 and ends on row 149. So I
manually type in 121 and 149 in the appropriate "F and G 29". Later, say
March gets new data and I have to insert rows for March. Now April starts on
row 126 and ends on 155, but the summary box still shows 121 and 149 until I
change it manually. How do I do it automatically?