SUMPRODUCT / INDEX MATCH Question

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

[TABLE="width: 396"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD]New York[/TD]
[TD]Pennsylvania[/TD]
[TD]Florida[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]Apples[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]13[/TD]
[/TR]
</tbody>[/TABLE]

Here is my data table, it is by city not state. Call this sheet 2

[TABLE="width: 608"]
<colgroup><col span="4"><col><col><col><col></colgroup><tbody>[TR]
[TD]Rep[/TD]
[TD]Product[/TD]
[TD]Buffalo[/TD]
[TD]Syracuse[/TD]
[TD]Harrisburg[/TD]
[TD]Scranton[/TD]
[TD]Miami[/TD]
[TD]Tampa Bay[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]Apples[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Kevin[/TD]
[TD]Bananas[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]Oranges[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Ted[/TD]
[TD]Apples[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]Oranges[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]Apples[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

Here is the table I need then to reference the city to each state. Table

[TABLE="width: 265"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]New York[/TD]
[TD]Pennsylvania[/TD]
[TD]Florida[/TD]
[/TR]
[TR]
[TD]Syracuse[/TD]
[TD]Harrisburg[/TD]
[TD]Miami[/TD]
[/TR]
[TR]
[TD]Buffalo[/TD]
[TD]Scranton[/TD]
[TD]Tampa Bay
[/TD]
[/TR]
</tbody>[/TABLE]


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!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.


Book1
ABCDE
1New YorkPennsylvaniaFlorida
2ChrisApples131413
3Kevinbananas6510
Sheet1
Cell Formulas
RangeFormula
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))}
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))}
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))}
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))}
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))}
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))}
Press CTRL+SHIFT+ENTER to enter array formulas.



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

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top