INdirect and row formula I THINK

[robcarr]

Hi,

I have a sheet with approx 400 rows of data on it.
at the top of the sheet is a summary of some of the data that is below, the formula used to get the data is

=SUMIF(51:51,$B$1,52:52)

this formula is in B39

in B40 i have

=SUMIF(72:72,$B$1,73:73)

and this works fine

row 51 has dates in it and b1 is the date it is loking up.

What I was wondering is there a way to add 21 rows to the formula for each cell so cell B41

=sumif(93:93,$B$1,94:94)

i have looked at the row formula and got the number to show but cant get it added into the sumif

=ROW(51:51)+B36 -B36 Contains 21 that is it.

Hope this is of use, Rob.

 

[Fenrirshowl]

You could try:

=SUMIF(INDIRECT(51+21*(ROW()-40) & ":" & 51+21*(ROW()-40)),$B$1,INDIRECT(52+21*(ROW()-40) & ":" & 52+21*(ROW()-40)))

or some variant.

Fen

 

[Fenrirshowl]

....in row 40 and then copy it down.