Excel formula to retrieve conditional sumproduct for massive data set

Question

I have a data table of over 50,000 rows. The data contains SALES information for permutations of STORES, DATES, and PRODUCTS. However, the PRODUCTS are actually a combination of PRIMARY PRODUCTS (PP) and SECONDARY PRODUCTS (SP), where a sale QUANTITY of 1 PP should convert to the sale of 1 or more SPs. I have another sheet containing the CONVERSIONS of PP to SP containing the respective MULTIPLIERS (over 500 rows). PPs and SPs have a many-to-many relationship; PPs may convert to several different SPs, and the same SP may be converted from other PPs.

At the moment, only unconverted sales quantities exist for the PRODUCTS, and it's my job to convert those figures to each PP's respective SP if a MULTIPLIER exists.

Sample: https://i.stack.imgur.com/YdGHn.png

I am able to do that with the following SUMPRODUCT() formula, which appears more efficient than an array formula:

=SUMPRODUCT(
(Conversions[Multiplier]),
--(Conversions[SP]=[@Product]),
SUMIFS([Quantity],[Product],Conversions[PP],[Store],[@Store],[Date],[@Date])
)

However, given the size of my data set, it still takes forever to process. Is there a more efficient way to do this?

EDIT:

I tried wrapping the formula in a conditional so that SUMPRODUCT only evaluates if the Product in question can be found in the Conversion table as an SP (and it also now displays the values of PRODUCTS that don't have any conversions). This seems to have sped things up a little, but still nowhere near quick enough...

=IFERROR(IF(MATCH([@Product],Conversions[SP],0)>0,
SUMPRODUCT(
(Conversions[Multiplier]),
--(Conversions[SP]=[@Product]),
SUMIFS([Quantity],[Product],Conversions[PP],[Store],[@Store],[Date],[@Date])
),0),0)+[@Quantity]

Have you considered using that formula in vba code to populate only the "new" products that have not been calculated — Solar Mike
@SolarMike It crossed my mind, but thing is I get an entirely new file like this everyday, so technically all product quantities are new. But I tried your suggestion in a different way; wrapped the formula in a conditional to check if the Product exists in the other table first before proceeding with the SUMPRODUCT. Thanks! — Jerry Lee
Maybe a pivot table could be a faster alternative, if that can give the results you are after. If using a database is not an option, then that's the next thing I'd recommend, otherwise, large amounts of data is bound to take longer to process. — Jerry
@Jerry I can see your suggestion probably being the best direction to go, but I'm stumped with how to go about it due to the many-to-many relationship between PP and SP. My brain runs out of power when I try to think of what fields to populate the pivot table with and where to go from there... :( — Jerry Lee

ywwy ywwy · Accepted Answer · 2019-02-19T07:13:02

if you have the possibility to import your data to a database. you then can work with indexed tables. should be faster.

Excel formula to retrieve conditional sumproduct for massive data set

1 Answers