SUMPRODUCT with skipping columns

Good day

A workbook contains 2 sheets.

Sheet 1 (REGISTER) contains a service register ID;

Sheet 2 (PLAN) contains the planned allocation of resources in each week of each month.

I would like to be able to have the total planned effort for each service by month in the REGISTER, however as the PLAN sheet has 4 columns for each month, when I copy the formula in the REGISTER sheet, the column reference moves from C:F to D:G instead of C:F to G:J.

Formula in Month 1 of REGISTER =SUMPRODUCT((PLAN!$A$3:$A$7=REGISTER!$A2)*PLAN!C$3:F$7)
Copied formula in Month 2 of REGISTER =SUMPRODUCT((PLAN!$A$3:$A$7=REGISTER!$A2)*PLAN!D$3:G$7)

The correct formula in Month 2 should be =SUMPRODUCT((PLAN!$A$3:$A$7=REGISTER!$A2)*PLAN!G$3:J$7)
and in Month 3 it should be =SUMPRODUCT((PLAN!$A$3:$A$7=REGISTER!$A2)*PLAN!K$3:N$7)


I need the cell reference to consider when I copy the formula to the next month it considers the skipping of columns.

Thank you
Junaid

Hi jhansrod. Welcome to the forum.

With the understanding that you will be referencing 12 months of data the source range would run across C$3:AX$7.
It also sounds like you want the output to run across columns A:L.

With that understanding replace that 4 column problem range (PLAN!C$3:F$7) to a dynamic range

INDEX(PLAN!C$3:AX$7,,(COLUMNS($A$1:A$1)-1)*4+1):INDEX(PLAN!C$3:AX$7,,(COLUMNS($A$1:A$1)-1)*4+4)

That will increment references by 4 columns with each column of output.

If that does not work you will need to upload a representative sample Excel file ... not screenshots or pics; saves retyping data.

Here's how to do it:

To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

Good work and nice catch on the absolute referencing error of mine.