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