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!
Bookmarks