Hey Everyone,
I've been stewing on this one for so long my head is steaming. I need a hand.
I have a workbook with five worksheets. Tab One is my output tab and has a number of cells that require a formula. Tab Two is a mapping table that looks something like this:
<tbody>
</tbody>
Tab Three is another mapping file:
<tbody>
</tbody>
Tab Four is where my Variance Data sits.
<tbody>
</tbody>
Tab Five is where my Sum Data sits.
<tbody>
</tbody>
On Tab One, my output tab, I need to sum together all of the intersections at Group 1 and Chicago (As well as 50+ other cells that contain some combination of Group and City, so hard-coding the mappings in the formula would not work for me). Members from Tab 2 with the Type of Variance need to be pulled from my Variance Tab, and those with the type of Sum need to be pulled from my Sum tab.
So I would expect the calculated value of my cell to equal 918 (18 from the Variance Tab plus 900 from the Sum tab).
I've tried creating a micro version of this formula using SUMPRODUCT, but I haven't been able to find a formula for SUMPRODUCT that takes multiple values as criteria. For instance: =SUMPRODUCT((A2:A5="Bee")*(B1:E1={"Three","Four"})*(B2:E5))
I receive #N/A when entering this formula for a single-table, small scale version of the functionality. Is there a way to pass a range to SUMPRODUCT? Or is this out of the capabilities of that function?
I've been stewing on this one for so long my head is steaming. I need a hand.
I have a workbook with five worksheets. Tab One is my output tab and has a number of cells that require a formula. Tab Two is a mapping table that looks something like this:
| A | B | C | |
| 1 | Name | Type | Group Mapping |
| 2 | Ayy | Variance | Group 1 |
| 3 | Bee | Sum | Group 1 |
| 4 | Cee | Sum | Group 3 |
| 5 | Dee | Sum | Group 3 |
| 6 | Eee | Variance | Group 3 |
| 7 | Eff | Sum | Group 4 |
<tbody>
</tbody>
Tab Three is another mapping file:
| A | B | |
| 1 | Name | City Mapping |
| 2 | One | Chicago |
| 3 | Two | Chicago |
| 4 | Three | New York |
| 5 | Four | Detroit |
| 6 | Five | Detroit |
| 7 | Six | Miami |
<tbody>
</tbody>
Tab Four is where my Variance Data sits.
| A | B | C | D | E | F | |
| 1 | Ayy | Bee | Ayy | Dee | Eee | |
| 2 | One | 1.0 | 2.0 | 3.0 | 4.0 | 5.0 |
| 3 | Two | 6.0 | 7.0 | 8.0 | 9.0 | 10.0 |
| 4 | Three | 11.0 | 12.0 | 13.0 | 14.0 | 15.0 |
| 5 | Three | 16.0 | 17.0 | 18.0 | 19.0 | 20.0 |
| 6 | Four | 21.0 | 22.0 | 23.0 | 24.0 | 25.0 |
<tbody>
</tbody>
Tab Five is where my Sum Data sits.
| A | B | C | D | E | F | |
| 1 | Ayy | Bee | Ayy | Dee | Eee | |
| 2 | One | 100.0 | 200.0 | 300.0 | 400.0 | 500.0 |
| 3 | Two | 600.0 | 700.0 | 800.0 | 900.0 | 1000.0 |
| 4 | Three | 1100.0 | 1200.0 | 1300.0 | 1400.0 | 1500.0 |
| 5 | Three | 1600.0 | 1700.0 | 1800.0 | 1900.0 | 2000.0 |
| 6 | Four | 2100.0 | 2200.0 | 2300.0 | 2400.0 | 2500.0 |
<tbody>
</tbody>
On Tab One, my output tab, I need to sum together all of the intersections at Group 1 and Chicago (As well as 50+ other cells that contain some combination of Group and City, so hard-coding the mappings in the formula would not work for me). Members from Tab 2 with the Type of Variance need to be pulled from my Variance Tab, and those with the type of Sum need to be pulled from my Sum tab.
So I would expect the calculated value of my cell to equal 918 (18 from the Variance Tab plus 900 from the Sum tab).
I've tried creating a micro version of this formula using SUMPRODUCT, but I haven't been able to find a formula for SUMPRODUCT that takes multiple values as criteria. For instance: =SUMPRODUCT((A2:A5="Bee")*(B1:E1={"Three","Four"})*(B2:E5))
I receive #N/A when entering this formula for a single-table, small scale version of the functionality. Is there a way to pass a range to SUMPRODUCT? Or is this out of the capabilities of that function?
