Shift column range to the right when dragging down

[Toby25]

Hi,

I need to shift a column range to the right by 5 when I drag down one row.

I.e. lets say I have values in A1:Z1 and I want to sum 5 cells at a time so

A2 = sum(A1:E1)
A3 = sum(F1:J1)
and so on

By following the guidance in this link I have managed to do this with ONE cell, but I need to move a range.

https://www.pcreview.co.uk/threads/increment-column-on-drag-down.2761586/

Thanks in advance.

 

[Toby25]

Hi,

Is this not possible to do?

Would really appreciate input

 

[Eric W]

In E20 of your sample sheet:

=COUNTIF(OFFSET(Sheet1!$E$9:$K$9,MATCH(C20,Sheet1!$C$9:$C$14,0)-1,($E$19-1)*7),"v")

Change the ranges to match your sheet, drag down as needed.

 

[Toby25]

In E20 of your sample sheet:

=COUNTIF(OFFSET(Sheet1!$E$9:$K$9,MATCH(C20,Sheet1!$C$9:$C$14,0)-1,($E$19-1)*7),"v")

Change the ranges to match your sheet, drag down as needed.

Thank you! This works perfect

What if I would like to skip weekends - everytime I have 7 days the last 2 aren't included in the count - So v'es entered on saturday and sunday aren't included?

This kind of messes up things.

 

[Eric W]

Just one small change:

=COUNTIF(OFFSET(Sheet1!$E$9:$I$9,MATCH(C20,Sheet1!$C$9:$C$14,0)-1,($E$19-1)*7),"v")

 

[Toby25]

Just one small change:

=COUNTIF(OFFSET(Sheet1!$E$9:$I$9,MATCH(C20,Sheet1!$C$9:$C$14,0)-1,($E$19-1)*7),"v")

Perfect!

Is there a reason why this wouldn't work with more criteria, i.e. {"P";"V"}? I'm having some trouble

 

[Toby25]

Never mind, found it - just need to sum them