# Thread: SUMPRODUCT / INDEX MATCH Question Thanks: 0 Likes: 0

1. ## SUMPRODUCT / INDEX MATCH Question

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!

2. ## Re: SUMPRODUCT / INDEX MATCH Question

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.

ABCDE
1New YorkPennsylvaniaFlorida
2ChrisApples131413
3Kevinbananas6510

Sheet1

Array Formulas
CellFormula
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))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

ABCDEFGH
1RepProductBuffaloSyracuseHarrisburgScrantonMiamiTampa Bay
2ChrisApples523404
3KevinBananas601428
4ChrisOranges451273
5TedApples664344
6MikeOranges011678
7ChrisApples424345
8
9
10
11New YorkPennsylvaniaFlorida
12SyracuseHarrisburgMiami
13BuffaloScrantonTampa Bay

Sheet2

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•