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

New YorkPennsylvaniaFlorida
ChrisApples131413

<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

RepProductBuffaloSyracuseHarrisburgScrantonMiamiTampa Bay
ChrisApples523404
KevinBananas601428
ChrisOranges451273
TedApples664344
MikeOranges011678
ChrisApples424345

<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 YorkPennsylvaniaFlorida
SyracuseHarrisburgMiami
BuffaloScrantonTampa 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!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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