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!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,611
Office Version
365, 2016
Platform
Windows
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">New York</td><td style=";">Pennsylvania</td><td style=";">Florida</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Chris</td><td style=";">Apples</td><td style="text-align: right;;">13</td><td style="text-align: right;;">14</td><td style="text-align: right;;">13</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Kevin</td><td style=";">bananas</td><td style="text-align: right;;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">10</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">{=SUMPRODUCT(<font color="Blue">IF(<font color="Red">Sheet2!$C$1:$H$1=Sheet2!A$12,Sheet2!$C$2:$H$7,IF(<font color="Green">Sheet2!$C$1:$H$1=Sheet2!A$13,Sheet2!$C$2:$H$7,0</font>)</font>),IF(<font color="Red">IF(<font color="Green">Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A2&$B2,Sheet2!$C$2:$H$7,0</font>)>0,1,0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">{=SUMPRODUCT(<font color="Blue">IF(<font color="Red">Sheet2!$C$1:$H$1=Sheet2!B$12,Sheet2!$C$2:$H$7,IF(<font color="Green">Sheet2!$C$1:$H$1=Sheet2!B$13,Sheet2!$C$2:$H$7,0</font>)</font>),IF(<font color="Red">IF(<font color="Green">Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A2&$B2,Sheet2!$C$2:$H$7,0</font>)>0,1,0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">{=SUMPRODUCT(<font color="Blue">IF(<font color="Red">Sheet2!$C$1:$H$1=Sheet2!C$12,Sheet2!$C$2:$H$7,IF(<font color="Green">Sheet2!$C$1:$H$1=Sheet2!C$13,Sheet2!$C$2:$H$7,0</font>)</font>),IF(<font color="Red">IF(<font color="Green">Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A2&$B2,Sheet2!$C$2:$H$7,0</font>)>0,1,0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">{=SUMPRODUCT(<font color="Blue">IF(<font color="Red">Sheet2!$C$1:$H$1=Sheet2!A$12,Sheet2!$C$2:$H$7,IF(<font color="Green">Sheet2!$C$1:$H$1=Sheet2!A$13,Sheet2!$C$2:$H$7,0</font>)</font>),IF(<font color="Red">IF(<font color="Green">Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A3&$B3,Sheet2!$C$2:$H$7,0</font>)>0,1,0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">{=SUMPRODUCT(<font color="Blue">IF(<font color="Red">Sheet2!$C$1:$H$1=Sheet2!B$12,Sheet2!$C$2:$H$7,IF(<font color="Green">Sheet2!$C$1:$H$1=Sheet2!B$13,Sheet2!$C$2:$H$7,0</font>)</font>),IF(<font color="Red">IF(<font color="Green">Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A3&$B3,Sheet2!$C$2:$H$7,0</font>)>0,1,0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E3</th><td style="text-align:left">{=SUMPRODUCT(<font color="Blue">IF(<font color="Red">Sheet2!$C$1:$H$1=Sheet2!C$12,Sheet2!$C$2:$H$7,IF(<font color="Green">Sheet2!$C$1:$H$1=Sheet2!C$13,Sheet2!$C$2:$H$7,0</font>)</font>),IF(<font color="Red">IF(<font color="Green">Sheet2!$A$2:$A$7&Sheet2!$B$2:$B$7=$A3&$B3,Sheet2!$C$2:$H$7,0</font>)>0,1,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Rep</td><td style=";">Product</td><td style=";">Buffalo</td><td style=";">Syracuse</td><td style=";">Harrisburg</td><td style=";">Scranton</td><td style=";">Miami</td><td style=";">Tampa Bay</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Chris</td><td style=";">Apples</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Kevin</td><td style=";">Bananas</td><td style="text-align: right;;">6</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Chris</td><td style=";">Oranges</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">7</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Ted</td><td style=";">Apples</td><td style="text-align: right;;">6</td><td style="text-align: right;;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Mike</td><td style=";">Oranges</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Chris</td><td style=";">Apples</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">New York</td><td style=";">Pennsylvania</td><td style=";">Florida</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Syracuse</td><td style=";">Harrisburg</td><td style=";">Miami</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Buffalo</td><td style=";">Scranton</td><td style=";">Tampa Bay</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,099,365
Messages
5,468,191
Members
406,570
Latest member
Ktvu2006

This Week's Hot Topics

Top