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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?

 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

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