Change SumIf range when copied across

[mahmed1]

Hi

i have a simple sumif formula in B2:B11 that
is = SumIfs(tel!$J$2:$J$11,tel!$A$2:$A$11,$A2)

What i need to is when i copy the formulas across to C2:C11
I need to move these ranges down 5 rows down from the end so
this
tel!$J$2:$J$11 to tel!$J$16:$J$25

tel!$A$2:$A$11 to tel!$A$16:$A$25

when copied to D2:D11

tel!$J$16:$J$25 to tel!$J$30:$J$39

tel!$A$16:$A$25 to tel!$A$30:$A$39

etc..

i hope ive explained properly

 

[mahmed1]

Please let me know if i have not explained properly

apologies if i have not

 

[jasonb75]

Depending on whether or not the $A2 criteria should follow the pattern as well, either

=SUMIFS(OFFSET(Tel!$J$2,(COLUMNS($B2:B2)-1)*14,0,10,1),OFFSET(Tel!$A$2,(COLUMNS($B2:B2)-1)*14,0,10,1),$A2)

or

=SUMIFS(OFFSET(Tel!$J$2,(COLUMNS($B2:B2)-1)*14,0,10,1),OFFSET(Tel!$A$2,(COLUMNS($B2:B2)-1)*14,0,10,1),OFFSET(Tel!$A2,(COLUMNS($B2:B2)-1)*14,0,10,1))

entered into B2, then filled right and down should do it.