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]

Hi. So do you mean if you drag the formula to the right you want the next column used for the sum range to be column O?

 

[thyriel]

If I drag the formula to the right the next column to get the data from on sheet 2 should be column T, I was just trying to make it work for the first column with the offset part added to the formula but haven't had any success

 

[thyriel]

sheet 1 is "oweninny" and sheet 2 is "Generation Summary View"

 

[steve the fish]

See if this works for column L then we can go from there.

=SUMIFS(INDEX('Generation Summary View'!$10:$17,,8+COLUMNS($A$1:A1)*4),'Generation Summary View'!$C$10:$C$17,Oweninny!$B$31)

Didnt you say every 4 columns? Why is the next column T?

 

[thyriel]

It no longer shows an error, but isn't importing any number either.

Apologies for before, I meant column P, and T afterwards. Thanks for the help so far.

 

[steve the fish]

Oh yes sorry it should be B32 at the end of the formula not B31. You may need to change the absolution if necessary when dragging.

 

[thyriel]

Your solution didn't seem to work. However, I managed to get it by changing the height of the offset from 7 to 8 which was the total height of the column being compared.

so the formula is now =SUMPRODUCT(--('Generation Summary View'!$C$10:$C$17=Oweninny!$<wbr>B31),(OFFSET('Generation Summary View'!$L$10,0,0,8,1)))

that said, I am now having issues when i try to offset it by 4 columns by modifying it as follows:

=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)))

It just gives a value error.

 

[steve the fish]

What does doesnt work mean? This formula:

=SUMPRODUCT(--('Generation Summary View'!$C$10:$C$17=Oweninny!B32),'Generation Summary View'!$L$10:$L$17)

and this:

=SUMIFS(INDEX('Generation Summary View'!$10:$17,,8+COLUMNS($A$1:A1)*4),'Generation Summary View'!$C$10:$C$17,Oweninny!$B$32)

will produce the same results. Are you saying they dont?

 

[thyriel]

They did after I removed the $ sign from $B32 and changed it to B31, since I'm veryfying if the values on Column C 10:17 on sheet to match the values on any of the rows on column B on sheet 1.

I'm also able to drag it across and get the values every 4th column, so thank you very much.

one last question though, just to have it as learning. why wouldn't the modified offset formula work when i used the column offset as opposed to a value?