Incrementing References by Multiples when Copying Formula

[cvetaneee]

Hello.

I have an excel file with two sheets.
Sheet1 contains my information and sheet2 is where I use my formula.

In sheet1, I have two columns, D and E, containing numbers. I need to count how many times, in each next 10 rows, the numbers in column D are bigger than the numbers in column E, and I need to perform the calculation in sheet2. I use a sumproduct function -> =sumproduct(--(sheet1!d3:d12>sheet1!e3:e12)) and then I want to drag the formula down. What I need to do is increment the reference range in the formula with 10 rows each time I drag the formula down a row, so that when I drag the formula down one row, my range would be d13:d22 and e13:e22 instead of d4:d13 and e4:e13 and so on.

I try to do that using and offset function into the sumproduct function but it doesn't work and returns #value! or #ref!

=SUMPRODUCT(--(OFFSET(sheet1!D3,(ROW()-2)*10-10,0,10,1))>(OFFSET(sheet1!E3,(ROW()-2)*10-10,0,10,1)))

I'm not at all well familiar with the offset function and I might well be using it wrong.

Please help and thanks a bunch!

[Crooza]

Totally untested as I'm on the iPad but try this

=sumproduct(--("sheet1!d"&(row(3:3)-3)*10+3 & ":d" & row(3:3)-3)*10+12 & ">sheet1!e" & (row(3:3)-3)*10+3 & ":e" & row(3:3)-3)*10+12))

[Phuocam]

Try
=SUMPRODUCT(--(INDEX(OFFSET(Sheet1!$D$3:$D$12,ROW(A1)*10-10,0),)>INDEX(OFFSET(Sheet1!$D$3:$D$12,ROW(A1)*10-10,1),)))
Phuoam

[cvetaneee]

Try
=SUMPRODUCT(--(INDEX(OFFSET(Sheet1!$D$3:$D$12,ROW(A1)*10-10,0),)>INDEX(OFFSET(Sheet1!$D$3:$D$12,ROW(A1)*10-10,1),)))
Phuoam

Awesome, this worked, thank you!

[jason.b75]

Try
=SUMPRODUCT(--(INDEX(OFFSET(Sheet1!$D$3:$D$12,ROW(A1)*10-10,0),)>INDEX(OFFSET(Sheet1!$D$3:$D$12,ROW(A1)*10-10,1),)))
Phuoam

No INDEX() needed, OFFSET works fine on its own

=SUMPRODUCT(--(OFFSET(Sheet1!$D$3,(ROW(A1)-1)*10,0,10,1)>OFFSET(Sheet1!$E$3,(ROW(A1)-1)*10,10,1)))

[Phuocam]

No INDEX() needed, OFFSET works fine on its own

=SUMPRODUCT(--(OFFSET(Sheet1!$D$3,(ROW(A1)-1)*10,0,10,1)>OFFSET(Sheet1!$E$3,(ROW(A1)-1)*10,10,1)))

Have you tried yet?

[jason.b75]

Have you tried yet?

A slight error because I edited your formula instead of retyping it properly.

The formula requires the use of ROWS, not ROW in order to prevent #VALUE! errors resulting from erroneous arrays.

=SUMPRODUCT(--(OFFSET(Sheet1!$D$3,(ROWS(A$1:A1)-1)*10,0,10,1)>OFFSET(Sheet1!$E$3,(ROWS(A$1:A1)-1)*10,0,10,1)))

[Phuocam]

A slight error because I edited your formula instead of retyping it properly.

The formula requires the use of ROWS, not ROW in order to prevent #VALUE! errors resulting from erroneous arrays.

=SUMPRODUCT(--(OFFSET(Sheet1!$D$3,(ROWS(A$1:A1)-1)*10,0,10,1)>OFFSET(Sheet1!$E$3,(ROWS(A$1:A1)-1)*10,0,10,1)))

Thank you, very good