Skipping multiple columns when filling formula to the right

[ShinyFirefly]

I have reviewed a couple of posts touching on this, but I'm afraid I'm not able to apply them to my situation because I don't want to skip every 'nth' column, but three columns.

I'm working with totaling the number of hours worked across a four-day work week on my raw data sheet representing a four day workweek (Monday-Thursday). The total of hours for that week goes on a new data sheet.

Right now the cell for that total on the new data sheet is
=+Personnel!KE40+Personnel!KF40+Personnel!KG40+Personnel!KH40

I'd like to be able to drag that right and fill, but I need to skip three columns (Friday, Saturday & Sunday) on the Personnel! sheet.

For reference, Personnel looks like this:

And the total of those columns needs to go in each week here:

And I'd like to be able to drag that formula to the right.

 

[Eric W]

Welcome to the Board.

For future reference, pictures are helpful for showing your sheet. But you don't show row/column references, so it makes it more difficult. And it would be FAR more helpful if you could use one of the HTML tools to post your data (see my signature). That would allow someone to actually copy your data into a test workbook and work on it. Many people won't take the time to manually type all your data into a test sheet.

That being said, let's say your first cell is in Sheet2!KE40, has 4 values, 3 zeros, 4 values, 3 zeros, etc. And let's say your formula is going to go into column KD on another sheet. Then try this:

=SUM(OFFSET(Sheet2!$KE40,0,(COLUMNS($KD:KD)-1)*7,1,4))

As you drag it to the right, it will sum up the 1st 4 cells, then the next non-zero 4 cells, etc.

Let me know if this works for you.

 

[ShinyFirefly]

Yes, it worked when I plugged in the correct columns I was working with. I was a little nervous about including too much (workplace rules) but I will definitely be sure to include column references in the future. I'll also review your HTML Maker to make sure I use the right tools next time. Thank you for the guidance and for the formula. Out of all the things I tried, OFFSET was not one of them.

 

[Eric W]

I understand some workplaces don't let you download files, or install files from the internet. So you have to do what you can. A more tedious way to show your data is with the table tool from the Advanced options. Make sure you don't include any proprietary information.

In any event, I'm glad it works for you!