Sumproduct with different array sizes

[skorpionkz]

I know there are many topics in this regards, but I couldn't find anything to answer my problem.

I have 2 tables as bellow. Table 1 is list of the product with price per range and discount rate.
Table 2 is list of the customer and order. I am trying to build formula that will dynamically adjust itself with Table 1 is expanded by more products, or additional ranges are added/removed to the existing product.

It is simple to add formula if there is only one product, but not sure how to make formula to work properly without calculating each customer separately and add them all together

=SUMPRODUCT(--(B21:F21>=Table1[Min No]),--(B21:F21<=Table1[Max No]),(B21:F21)*(Table1[Price]))
where B21-F21 Table 2 orders for all customer for product A.
Question is, is it possible to adjust this formula to add condition to sum only appropriate product?
obviously this didn't works as I am matching 1 value to the array for product but the array to array to get price.
=SUMPRODUCT(--(A21=Table1[Product]),--(B21:F21>=Table1[Min No]),--(B21:F21<=Table1[Max No]),(B21:F21)*(Table1[Price]))

Product	Min No	Max No	Price	% Discount
Prodauct A	0	0	€0​	0%
Prodauct A	1	1	€200​	0%
Prodauct A	2	10	€180​	10%
Prodauct A	11	20	€170​	15%
Prodauct B	0	0	€0​	0%
Prodauct B	1	1	€150​	0%
Prodauct B	2	20	€144​	4%
Prodauct B	21	50	€138​	8%
Prodauct B	51	100	€135​	10%
Prodauct B	101	300	€132​	12%
Prodauct C	0	0	€0​	0%
Prodauct C	1	1	€100​	0%
Prodauct C	2	20	€97​	3%
Prodauct C	21	50	€96​	4%
Prodauct C	51	100	€95​	5%
Prodauct C	101	300	€94​	6%

Product	Cust 1	Cust 2	Cust 3	Cust 4	Cust 5	Total value
Prodauct A	2	3	5	5	5	=SUMPRODUCT(--(B21:F21>=Table1[Min No]),--(B21:F21<=Table1[Max No]),(B21:F21)*(Table1[Price]))
Prodauct B	20	80	150	200	250
Prodauct C	10	50	130	150	200

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUMPRODUCT(--(B21:F21>=Table1[Min No]),--(B21:F21<=Table1[Max No]),(B21:F21)*(Table1[Price]))

 

[skorpionkz]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUMPRODUCT(--(B21:F21>=Table1[Min No]),--(B21:F21<=Table1[Max No]),(B21:F21)*(Table1[Price]))

Sorry I cannot see changes to the formula. I am trying to add product check. I believe the Excel version is in my signature, though it change yet to 2016.

 

[Fluff]

Oops, forgot to copy the changed formula, it should be

=SUMPRODUCT((B21:F21>=Table1[Min No])*(B21:F21<=Table1[Max No])*(A21=Table1[Product]),(B21:F21)*(Table1[Price]))

 

[skorpionkz]

Oops, forgot to copy the changed formula, it should be

=SUMPRODUCT((B21:F21>=Table1[Min No])*(B21:F21<=Table1[Max No])*(A21=Table1[Product]),(B21:F21)*(Table1[Price]))

Did the Trick! Thank you

 

[Fluff]

You're welcome & thanks for the feedback.