Double Lookup using SUMPRODUCT

tigrou

New Member
Joined
Jan 24, 2003
Messages
24
Can anyone help with this please? I have the formula below that looks up a total value (usually just one value but sometimes two or three) in worksheet 'Coldstore' range F101:F180, between a range (a combination of date+time) in worksheet 'Product Analysis Early 2'

=SUMPRODUCT((Coldstore!F$101:F$180>='Product Analysis Early 2'!AQ6)*(Coldstore!$F$101:$F$180<='Product Analysis Early 2'!AQ11)*(Coldstore!$I$101:$I$180))

This works fine for a grand total if there is just one figure in 'Coldstore' range F101:F180 relating to one product code in 'Coldstore' range E101:E180, sometimes there can be another figure relating to a different product code in the same range of time. The product codes are 41291, 41292 and 41293. I would like to use another lookup to be able to total the figure by specific product code. Thanks in anticipation. :hungry:
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Assuming your product code is in c column, try this:

=SUMPRODUCT((Coldstore!C$101:C$180=41219)*(Coldstore!F$101:F$180>='Product Analysis Early 2'!AQ6)*(Coldstore!$F$101:$F$180<='Product Analysis Early 2'!AQ11)*(Coldstore!$I$101:$I$180))
 
Upvote 0
Hi tigrou:

How about posting some sample data using HTMLmaker (Colo's Cool utility) downloadable from this MrExcel page -- to see the layout of your data and what you are trying to accomplish.
 
Upvote 0
fairwinds said:
Assuming your product code is in c column, try this:

=SUMPRODUCT((Coldstore!C$101:C$180=41219)*(Coldstore!F$101:F$180>='Product Analysis Early 2'!AQ6)*(Coldstore!$F$101:$F$180<='Product Analysis Early 2'!AQ11)*(Coldstore!$I$101:$I$180))

Is the 41219 a true number, or is it a Text representation of a number?
 
Upvote 0
fairwinds said:
Then please explain clearly where your product codes (and other info) are located.
Apologies for not replying sooner - IT DID WORK! Just that the column I wanted the results from (the numeric total) had a formula in to calculate the result. I copied the numeric result into another column and looked up that column instead. Tout va bien qui fini bien. Thanks to all involved.
 
Upvote 0
The fact that it was a value derived from a formula should not matter. Wonder what was going on?
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
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