Double Lookup using SUMPRODUCT

[tigrou]

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.

 

[fairwinds]

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

 

[tigrou]

Thanks so much for your kind attention, unfortunately couldn't get your suggestion to work.

 

[fairwinds]

Then please explain clearly where your product codes (and other info) are located.

 

[Yogi Anand]

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.

 

[just_jon]

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?

 

[tigrou]

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.

 

[just_jon]

The fact that it was a value derived from a formula should not matter. Wonder what was going on?