Sumproduct and offset issues

[thyriel]

Hi,

I managed to have certain cells of my sheet1 reference others from sheet2 If a condition was met by using sumproduct. I managed to get this reference by using offset as I wanted to automate it by having it index numbers every 4 columns. it went smoothly while I was using no column offset with the following formula:


=SUMPRODUCT(--('Generation Summary View'!$C$10:$C$17=Oweninny!$<wbr style="font-family: Arial, Helvetica, sans-serif; font-size: small;">B31),(OFFSET('Generation Summary View'!$L$10,0,0,8,1)))

However, when i tried to have it offset every 4 columns it gives me a value error. The modified formula is this:

=SUMPRODUCT(--('Generation Summary View'!$C$10:$C$17=Oweninny!$<wbr>B31),(OFFSET('Generation Summary View'!$L$10,0,(COLUMN(A1)*4)-<wbr>4,8,1)))

for clarity purposes, Oweninny is sheet1 and Generation Summary View is sheet2, the data i'm trying to get is the numbers from column L 10:17 from sheet2 and the condition is that the numbers on column C on sheet2 match those on column B on sheet1.

As I said, the first formula works flawlessly but when I actually try to make it offset every 4th row it shows an error.

thanks
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">=SUMPRODUCT(--('Generation Summary View'!$C$10:$C$17=Oweninny!$<wbr style="font-family: Arial, Helvetica, sans-serif; font-size: small;">B31),(OFFSET('Generation Summary View'!$L$10,0,0,8,1)))</body>

 

[steve the fish]

Please dont open a new thread as that wastes peoples time.

https://www.mrexcel.com/forum/excel-questions/1110609-issues-using-sumproduct-offset.html

 

[thyriel]

apologies. will close this one and reply on previous