Issues using Sumproduct with Offset

thyriel

New Member
Joined
Sep 24, 2019
Messages
8
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
 
Instead of using COLUMN(A1) use COLUMNS($A$1:A1) instead. It should work then.
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top