Sumproduct with different array sizes

skorpionkz

Well-known Member
Joined
Oct 1, 2013
Messages
1,171
Office Version
  1. 2016
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]))

ProductMin NoMax NoPrice% Discount
Prodauct A00
€0​
0%
Prodauct A11
€200​
0%
Prodauct A210
€180​
10%
Prodauct A1120
€170​
15%
Prodauct B00
€0​
0%
Prodauct B11
€150​
0%
Prodauct B220
€144​
4%
Prodauct B2150
€138​
8%
Prodauct B51100
€135​
10%
Prodauct B101300
€132​
12%
Prodauct C00
€0​
0%
Prodauct C11
€100​
0%
Prodauct C220
€97​
3%
Prodauct C2150
€96​
4%
Prodauct C51100
€95​
5%
Prodauct C101300
€94​
6%

ProductCust 1Cust 2Cust 3Cust 4Cust 5Total value
Prodauct A23555=SUMPRODUCT(--(B21:F21>=Table1[Min No]),--(B21:F21<=Table1[Max No]),(B21:F21)*(Table1[Price]))
Prodauct B2080150200250
Prodauct C1050130150200
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
Excel Formula:
=SUMPRODUCT(--(B21:F21>=Table1[Min No]),--(B21:F21<=Table1[Max No]),(B21:F21)*(Table1[Price]))
 
Upvote 0
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
Excel Formula:
=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.
 
Upvote 0
Oops, forgot to copy the changed formula, it should be
Excel Formula:
=SUMPRODUCT((B21:F21>=Table1[Min No])*(B21:F21<=Table1[Max No])*(A21=Table1[Product]),(B21:F21)*(Table1[Price]))
 
Upvote 0
Oops, forgot to copy the changed formula, it should be
Excel Formula:
=SUMPRODUCT((B21:F21>=Table1[Min No])*(B21:F21<=Table1[Max No])*(A21=Table1[Product]),(B21:F21)*(Table1[Price]))
Did the Trick! Thank you
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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