sumproduct or index & match question

Joneye

Joneye

Well-known Member
Joined
May 28, 2010
Messages
785
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
In cell B3 the answer should be 4 (Refers to C:14-C:17). Its a simple one I hope my brain has crashed and ive lost all powers to do SUMPRODUCT today.

ANy help would be great failing that, im sure ill work it out after 12 hours sleep.


<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Development 2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">Volume</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">house 1</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">house 2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">house 3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">house 4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</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: #161120;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></tr><tr ><td style="color: #161120;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></tr><tr ><td style="color: #161120;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></tr><tr ><td style="color: #161120;text-align: center;">11</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: #161120;text-align: center;">12</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: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style=";">Development 1</td><td style=";">Development 2</td><td style=";">Development 3</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">house 1</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">house 2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">12</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">house 3</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">house 4</td><td style="text-align: right;;">9</td><td style="text-align: right;;">4</td><td style="text-align: right;;">15</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">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: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">A1</th><td style="text-align:left">=C13</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">B13:D13=A1</font>)*(<font color="Red">B14:D17=A3</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
This is an INDEX/MATCH solution:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #FFFFFF;;">Development 2</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="background-color: #FFFFFF;;">Volume</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #FFFFFF;;">house 1</td><td style="text-align: right;background-color: #FFFFFF;;">4</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #FFFFFF;;">house 2</td><td style="text-align: right;background-color: #FFFFFF;;">12</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="background-color: #FFFFFF;;">house 3</td><td style="text-align: right;background-color: #FFFFFF;;">4</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="background-color: #FFFFFF;;">house 4</td><td style="text-align: right;background-color: #FFFFFF;;">4</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="background-color: #FFFFFF;;">Development 1</td><td style="background-color: #FFFFFF;;">Development 2</td><td style="border-right: 1px solid black;background-color: #FFFFFF;;">Development 3</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="background-color: #FFFFFF;;">house 1</td><td style="text-align: right;background-color: #FFFFFF;;">10</td><td style="text-align: right;background-color: #FFFFFF;;">4</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="background-color: #FFFFFF;;">house 2</td><td style="text-align: right;background-color: #FFFFFF;;">1</td><td style="text-align: right;background-color: #FFFFFF;;">12</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="background-color: #FFFFFF;;">house 3</td><td style="text-align: right;background-color: #FFFFFF;;">5</td><td style="text-align: right;background-color: #FFFFFF;;">4</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="border-bottom: 1px solid black;background-color: #FFFFFF;;">house 4</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;">9</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;">4</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">15</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Data</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=INDEX(<font color="Blue">$B$14:$D$17,MATCH(<font color="Red">A3,$A$14:$A$17,0</font>),MATCH(<font color="Red">$A$1,$B$13:$D$13</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
Joneye said:
In cell B3 the answer should be 4 (Refers to C:14-C:17). Its a simple one I hope my brain has crashed and ive lost all powers to do SUMPRODUCT today.

ANy help would be great failing that, im sure ill work it out after 12 hours sleep.


<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Development 2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">Volume</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">house 1</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">house 2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">house 3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">house 4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</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: #161120;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></tr><tr ><td style="color: #161120;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></tr><tr ><td style="color: #161120;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></tr><tr ><td style="color: #161120;text-align: center;">11</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: #161120;text-align: center;">12</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: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style=";">Development 1</td><td style=";">Development 2</td><td style=";">Development 3</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">house 1</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">house 2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">12</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">house 3</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">house 4</td><td style="text-align: right;;">9</td><td style="text-align: right;;">4</td><td style="text-align: right;;">15</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">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: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">A1</th><td style="text-align:left">=C13</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">B13:D13=A1</font>)*(<font color="Red">B14:D17=A3</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Click to expand...
If the intent is retrieval...

=INDEX($B$14:$D$17,MATCH($A3,$A$14:$A$17,0),MATCH($A$1,$B$13:$D$13,0))

If it's summing, which would presuppose multiple records satisfying the stated conditions like in:

=SUMIF($A$14:$A$17,$A3,INDEX($B$14:$D$17,0,MATCH($A1,$B$13:$D$13,0)))
 
Upvote 0
Guys many thanks, again it all helps.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
616
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
R
  • Question Question
import specific columns from one workbook to another from matching header with vba
Replies
1
Views
130
Peter_SSs
Peter_SSs
I
  • Question Question
Sum column by priority (Y-axis) and date (Y-axis)
Replies
3
Views
224
kvsrinivasamurthy
K
E
  • Question Question
Adding a custom message.
Replies
1
Views
659
Micron
M

Forum statistics

Threads
1,224,514
Messages
6,179,223
Members
452,896
Latest member
IGT

Share this page

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