Conditional SUM difficulty in Excel

1
votes

I have the following problem: I need to sum the values in the three columns "Value", and at the same time calculate the discount rate for all items that are marked with the "YES" flag.

The rule for calculating the discount is as follows: For each item marked, separate the word before the "To" tab and search the "Products" table just below the main table. (See attached picture)
Comments:

  1. The construction "Fruit To ..." is standardized, it will always occur in this way;
  2. All marked items must have a corresponding line in the "Fruits" table;
  3. The two tables have different amount of rows;
    I already tried all possible combinations of matrix sum, INDEX+MATCH, VLOOKUP, SUMPRODUCT, etc. which I could imagine and could not come up with in a formula that worked.

If someone can see a way that I have not tried yet and that works, thank you in advance for any help!

Angelino

enter image description here

2
excel
So the rows without "YES" flag should not be added into the sum? Or should they be added as the origin value? – newacc2240

2 Answers

1
votes

Try following which would sum adding discount where flag is Yes. You can amend ranges according to number of rows present in sum range and lookup range.

 {=SUMPRODUCT(C7:C17*IF($B$7:$B$17="Yes",VLOOKUP(LEFT($A$7:$A$17$,FIND(" ",$A$7:$A$17$)-1),$A$20:$B$22,2,FALSE),1)) + SUMPRODUCT(D7:D17*IF($B$7:$B$17="Yes",VLOOKUP(LEFT($A$7:$A$17$,FIND(" ",$A$7:$A$17$)-1),$A$20:$B$22,2,FALSE),1)) + SUMPRODUCT(E7:E17*IF($B$7:$B$17="Yes",VLOOKUP(LEFT($A$7:$A$17$,FIND(" ",$A$7:$A$17$)-1),$A$20:$B$22,2,FALSE),1))}
0
votes

Using SUMIF as a "pseudo lookup" to get the percentages, you can use this formula

=SUMPRODUCT(SUMIF(A20:A22,LEFT(A7:A17,FIND(" ",A7:A17)-1),B20:B22)*(B7:B17="Yes")*C7:E17)