Drag formula horizontally but have cell number increment

[Quantus]

Hello there, I would be very grateful if someone could help me!

I want to increase the row number of range of cells with 1 row, while drag formula horizontally with 1 cell right.
For example: =SUM(A1:G1) to become =SUM(A2:G2) when drag the formula right.
The big problem for me is that the cells range refers to another sheet and it is inside the sumproduct function:
For example:
CELL A2 | CELL B2 | CELL C2
=SUMPRODUCT(--($A$1=Sheet2!$A$1:$G$1); Sheet2!$A11:$G11) | =SUMPRODUCT(--($A$1=Sheet2!$A$1:$G$1); Sheet2!$A12:$G12) | =SUMPRODUCT(--($A$1=Sheet2!$A$1:$G$1); Sheet2!$A13:$G13)

 

[Quantus]

Any ideas???

 

[KRice]

Try this...

=SUMPRODUCT(--($A$1=Sheet2!$A$1:$G$1),OFFSET(Sheet2!$A11,COLUMNS($A:A)-1,,,7))

The OFFSET function establishes Sheet2!$A11 as a reference point in your first cell and then offsets some number of rows from that reference point based on the number of columns you've pulled the formula across....always keeping the total number of columns to be considered on Sheet2 fixed at 7.

I'm not sure why you've marked your follow up as a solution?

 

[Quantus]

Hi, thank you!
I have marked it, because I didn't find how to delete my thread, as I have already found a solution...
In cell A3 =SUMPRODUCT(--($A$1=Sheet2!$A$1:$G$1)*(A$2=Sheet2!$A11:$A13); Sheet2!$A11:$G13)

 

[KRice]

Thanks for the response. In the future, if you find a solution to your question, best practice is to post your own solution and then mark it as the solution so that others know further attention is not needed.

A second point: I don't see how what you've marked as a solution solves the problem you posted. As you drag that formula right, the row numbers will not increase, which was the desired behavior you sought. Instead, the row numbers remain fixed at Sheet2!$A11:$G13. Perhaps you don't really want what you initially asked for and including the full range (of rows 11:13) is okay? I don't know without more detail...this is why posting a small example of your worksheet(s) using the XL2BB add-in would offer a clearer description of what you would like to do.

If you investigate my solution, be aware that my regional settings use commas to separate arguments. Yours appears to use semicolons, so you may need to do some minor editing.

 

[Peter_SSs]

A second point: I don't see how what you've marked as a solution solves the problem you posted.

I agree, so have removed the 'Mark as solution.