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.

 

[Eric W]

Welcome to the board.

Put this in A2 and drag down:

=SUM(OFFSET($A$1:$E$1,0,(ROWS($A$2:$A2)-1)*5))

 

[Toby25]

Welcome to the board.

Put this in A2 and drag down:

=SUM(OFFSET($A$1:$E$1,0,(ROWS($A$2:$A2)-1)*5))

Thanks! This works like a charm!

However, I can't seem to get it to work with the COUNTIF function. I have a case where I would like to count the number of v's in a range of 5 - but if I go

=COUNTIF(OFFSET($A$1:$E$1,0,(ROWS($A$2:$A2)-1)*5),"V")

It doesn't seem to work?

 

[Eric W]

What doesn't seem to work about it? Error, wrong result? I copied your formula exactly and pasted into my workbook, and it worked fine for me.

 

[Toby25]

Aaah, I found the problem - It was in this part "($A$2:$A2)" where i accidentally started having ($A$2:$A3).

Thanks for helping out!

 

[Toby25]

Hello again, @Eric W

Is it possible to combine this formula i.e.

=COUNTIF(OFFSET($A$1:$E$1,0,(ROWS($A$2:$A2)-1)*5),"V")

with an INDEX MATCH solution.

*See image - I have the following formula in sheet 2 (yellow)

=COUNTIF(OFFSET(Sheet1!$D$14:$H$14,0,(ROWS($A$2:$A2)-1)*5),"V")

And instead of specifying manually which row to look in (Name1 = row 14) I would like it to search through the names in sheet1 based on the name found in sheet 2 (i.e. Name1 = D24) and return the range/interval corresponding to that name (i.e. Name3 = D$16:$H$16) - so changes to the name list won't result in lots of extra work.

Hope you can help!

Image: https://imgur.com/UEfEQc0

 

[Toby25]

 

[Toby25]

Picture with columns/rows

https://imgur.com/hunu2Kk

 

[Eric W]

Sure, in your D25 cell:

=COUNTIF(OFFSET(Sheet1!$D$14:$H$14,MATCH(D$24,Sheet1!$B$14:$B$18,0)-1,(ROWS($A$25:$A25)-1)*5),"V")

drag down and across as needed.

The 0 from your original formula says how many rows down to go. We just replace that with the MATCH, and we find the matching row instead.

 

[Toby25]

Sure, in your D25 cell:

=COUNTIF(OFFSET(Sheet1!$D$14:$H$14,MATCH(D$24,Sheet1!$B$14:$B$18,0)-1,(ROWS($A$25:$A25)-1)*5),"V")

drag down and across as needed.

The 0 from your original formula says how many rows down to go. We just replace that with the MATCH, and we find the matching row instead.

Hello again, @Eric W

Thanks again - this worked out just perfect!

However, after some further work it would be neater to "skip a sheet"

In short, what I would like is shown in this picture

https://imgur.com/NundSyK

With the highlighted cell working as an input field/ being dynamic.

The challenge for me is how to "lookup" and count the v's in the range corresponding to week 1, week 2 etc., based on the week number specified in sheet 2.

Furthermore, in the "raw" data sheet (vacationplan) (sheet 1) I actually doesn't have weeks specified at the top; I simply has all the days/weeks in a given year i.e. starting at 1/1/2017 to 31/12/2017.