Sumproduct Workaround

Hi,

I'm trying to use Sumproduct to lookup up a unique value in a range of cells. The range is for updated costs with the first row containing the date, the second row containing the supplier and the first column containing the product. This is what I have so far:

=SUMPRODUCT((a2:a10=PRODUCT)*(b1:g1<=DATE)*(b2:g2=SUPPLIER),b3:g10)

I want the formula to find the most recent entry after DATE, however I've only been able to sum the and entries previous to DATE. Is there a way to add another criteria to only sum/lookup the most recent entry?

Thanks

Hi,

Thanks for the welcome. I've made up a mockup with my formula highlighted in yellow and the desired result/prices next to it.


Most Recent Price Before Date.xlsx

Originally Posted by daddylonglegs

Assuming that dates don't repeat try this "array formula"

=SUM((B1:G1=MAX((B1:G1<=DATE)*(B2:G2=SUPPLIER)))*(A2:A10=PRODUCT)*B3:G10)

confirmed with CTRL+SHIFT+ENTER

Hi,

I've tried that out it doesn't seem to work for me. I'll double check that it's not an issue with my entries.

That's great, thanks a million. Was stuck on it all day.

Hi DDL,

Sorry for bringing this up again, but I'm having a small issue with this again. Whilst the formula works perfectly, it has become CPU intensive as the formula is used in 15,000 cells. Is there anyway to make it more CPU friendly?

Thanks again