Exquestions215
New Member
- Joined
- Sep 20, 2019
- Messages
- 1
Hello,
I'm trying to use a sumproduct formula to pull in data from a table with 3 criteria, but I need one criteria to reference a table. Here's an example of what I'm trying to do.
I'm trying to pull how many apples Chris sold in each state. Sheet 1
<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>
Here is my data table, it is by city not state. Call this sheet 2
<colgroup><col span="4"><col><col><col><col></colgroup><tbody>
</tbody>
Here is the table I need then to reference the city to each state. Table
<colgroup><col><col><col></colgroup><tbody>
</tbody>
I can do a sumproduct formula like this =SUMPRODUCT((Sheet2!B2:H7)*(Sheet2!A1:A7=Sheet1!A2)*(Sheet2!B1:B7=Sheet1!B2)*(Sheet2!A1:H1=Sheet1!C3),(C3 being a city not a state) that I could reference a city to work, but I can't seem to get it to reference the state table to pull in a city at once.
Any ideas, I've tried doing an index match as the third condition referencing the state table but have had no luck.
Thanks!
I'm trying to use a sumproduct formula to pull in data from a table with 3 criteria, but I need one criteria to reference a table. Here's an example of what I'm trying to do.
I'm trying to pull how many apples Chris sold in each state. Sheet 1
New York | Pennsylvania | Florida | ||
Chris | Apples | 13 | 14 | 13 |
<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>
Here is my data table, it is by city not state. Call this sheet 2
Rep | Product | Buffalo | Syracuse | Harrisburg | Scranton | Miami | Tampa Bay |
Chris | Apples | 5 | 2 | 3 | 4 | 0 | 4 |
Kevin | Bananas | 6 | 0 | 1 | 4 | 2 | 8 |
Chris | Oranges | 4 | 5 | 1 | 2 | 7 | 3 |
Ted | Apples | 6 | 6 | 4 | 3 | 4 | 4 |
Mike | Oranges | 0 | 1 | 1 | 6 | 7 | 8 |
Chris | Apples | 4 | 2 | 4 | 3 | 4 | 5 |
<colgroup><col span="4"><col><col><col><col></colgroup><tbody>
</tbody>
Here is the table I need then to reference the city to each state. Table
New York | Pennsylvania | Florida |
Syracuse | Harrisburg | Miami |
Buffalo | Scranton | Tampa Bay |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
I can do a sumproduct formula like this =SUMPRODUCT((Sheet2!B2:H7)*(Sheet2!A1:A7=Sheet1!A2)*(Sheet2!B1:B7=Sheet1!B2)*(Sheet2!A1:H1=Sheet1!C3),(C3 being a city not a state) that I could reference a city to work, but I can't seem to get it to reference the state table to pull in a city at once.
Any ideas, I've tried doing an index match as the third condition referencing the state table but have had no luck.
Thanks!