I have two tables on separate worksheets: one is a list of options and corresponding selection values, the second is a list of line items and costs. I'm trying to come up with a formula which will total all costs for selected options only. I'd like to avoid using a helper column if possible.
Worksheet 1, Option Selections:
+---+-----------+----------+
| | A | B |
+---+-----------+----------+
| 1 | Option | Selected |
| 2 | Option #1 | No |
| 3 | Option #2 | Yes |
| 4 | Option #3 | Yes |
| 5 | Option #4 | No |
| 6 | Option #5 | Yes |
+---+-----------+----------+
Worksheet 2, Line Items:
+---+-----------+--------------+-------+
| | A | B | C |
+---+-----------+--------------+-------+
| 1 | Option | Line item | Cost |
| 2 | Option #1 | Apples | 5.00 |
| 3 | Option #1 | Bananas | 3.00 |
| 4 | Option #3 | Raspberries | 4.50 |
| 5 | Option #2 | Oranges | 9.00 |
| 6 | Option #5 | Lemons | 2.50 |
| 7 | Option #4 | Strawberries | 6.00 |
| 8 | | Total | 16.00 |
+---+-----------+--------------+-------+
I'd like a formula for Cell C8 on Worksheet 2 which would total only line items associated with "Yes" options. There may be multiple line items per option and the line item table will not necessarily be ordered by Column A.
I've tried combinations of Sumproduct with Index/Match and Sumifs with Index/Match but I've been unsuccessful.
Edit: I have been able to get it to work with the following formula, but only when I change the "Yes" and "No" values to 1's and 0's. I'd like to use Yes and No though. Can anyone help me adapt this formula to work as intended?
=SUMPRODUCT(SUMIF(Table1[Option],Table2[Option],Table1[Selected]),Table2[Cost])
I tried changing to this formula to convert the range to 1's and 0's, but this throws an error:
=SUMPRODUCT(SUMIF(Table1[Option],Table2[Option],--(Table1[Selected]="Yes"),Table2[Cost])
