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
New York Pennsylvania Florida Chris Apples 13 14 13
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
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
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!
Since this formula does not reference the states on sheet 1 you will need make sure it refrences the correct cities on sheet 2. If they are listed in the same order you can copy across.
A B C D E 1 New York Pennsylvania Florida 2 Chris Apples 13 14 13 3 Kevin bananas 6 5 10 Sheet1
Array Formulas Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Cell Formula C2 {=SUMPRODUCT(IF(Sheet2!$C$1:$H$1=Sheet2!A$12,Sheet2!$C$2:$H$7,IF(Sheet2!$C$1:$H$1=Sheet2!A$13,Sheet2!$C$2:$H$7,0)),IF(IF(Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A2&$B2,Sheet2!$C$2:$H$7,0)>0,1,0))} D2 {=SUMPRODUCT(IF(Sheet2!$C$1:$H$1=Sheet2!B$12,Sheet2!$C$2:$H$7,IF(Sheet2!$C$1:$H$1=Sheet2!B$13,Sheet2!$C$2:$H$7,0)),IF(IF(Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A2&$B2,Sheet2!$C$2:$H$7,0)>0,1,0))} E2 {=SUMPRODUCT(IF(Sheet2!$C$1:$H$1=Sheet2!C$12,Sheet2!$C$2:$H$7,IF(Sheet2!$C$1:$H$1=Sheet2!C$13,Sheet2!$C$2:$H$7,0)),IF(IF(Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A2&$B2,Sheet2!$C$2:$H$7,0)>0,1,0))} C3 {=SUMPRODUCT(IF(Sheet2!$C$1:$H$1=Sheet2!A$12,Sheet2!$C$2:$H$7,IF(Sheet2!$C$1:$H$1=Sheet2!A$13,Sheet2!$C$2:$H$7,0)),IF(IF(Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A3&$B3,Sheet2!$C$2:$H$7,0)>0,1,0))} D3 {=SUMPRODUCT(IF(Sheet2!$C$1:$H$1=Sheet2!B$12,Sheet2!$C$2:$H$7,IF(Sheet2!$C$1:$H$1=Sheet2!B$13,Sheet2!$C$2:$H$7,0)),IF(IF(Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A3&$B3,Sheet2!$C$2:$H$7,0)>0,1,0))} E3 {=SUMPRODUCT(IF(Sheet2!$C$1:$H$1=Sheet2!C$12,Sheet2!$C$2:$H$7,IF(Sheet2!$C$1:$H$1=Sheet2!C$13,Sheet2!$C$2:$H$7,0)),IF(IF(Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A3&$B3,Sheet2!$C$2:$H$7,0)>0,1,0))}
Note: Do not try and enter the {} manually yourself
A B C D E F G H 1 Rep Product Buffalo Syracuse Harrisburg Scranton Miami Tampa Bay 2 Chris Apples 5 2 3 4 0 4 3 Kevin Bananas 6 0 1 4 2 8 4 Chris Oranges 4 5 1 2 7 3 5 Ted Apples 6 6 4 3 4 4 6 Mike Oranges 0 1 1 6 7 8 7 Chris Apples 4 2 4 3 4 5 8 9 10 11 New York Pennsylvania Florida 12 Syracuse Harrisburg Miami 13 Buffalo Scranton Tampa Bay Sheet2
Colonel Sandurz: Prepare ship for light speed.
Dark Helmet: No, no, no, light speed is too slow.
Colonel Sandurz: Light speed, too slow?
Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.
Like this thread? Share it with others