Issues using Sumproduct with Offset

[thyriel]

Hi all,

I'm having issues with a file I'm working with.

to make it brief. I'm trying to get data from a second sheet (1) into my main sheet (2). the second sheet has data of budget spent every month for a range of work numbers, the total spent (which is the figure I want) is every 4th column.

The first step was making sure the formula imported the numbers from sheet 2 only if the work number matched the row on sheet 1. I achieved this with sumproduct using this formula:

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

as far as I understand, it verifies the numbers on column C of sheet 2 match the numbers of column B on sheet 1 and returns a one if true, which gets multiplied by the array on column L on sheet 2, hence "importing" the numbers I want onto my main sheet.

Now I wanted to have this happen for every month. the way sheet 1 is set up there is a month every column, but on sheet 2 the figures I want are every 4th column, I thought i could achieve this by using the offset function, so I attempted to modify the formula I used before on a very basic level first to see if I got the same numbers. so I used:

=SUMPRODUCT(--('Generation Summary View'!$C$10:$C$17=Oweninny!<wbr>B31),(OFFSET('Generation Summary View'!$C10,0,9,,7)))



for now I have the row typed with number 9 for my first attempt and would change it to "column(-1)*x" once I get this to work, but haven't been able to so far. any advice?

thanks

 

[steve the fish]

Instead of using COLUMN(A1) use COLUMNS($A$1:A1) instead. It should work then.