Retrieve value based on 3 criteria. (2 rows, 1 column)

Klove86

New Member
Joined
Feb 18, 2016
Messages
3
Hello,

I have some trouble reviving the right value from a range when use 3 criteria: 2 rows and 1 column.

My sheet looks like the(just in a bigger scale)

Per Lars Per Henriette
2800 2900 2900 3100
D01 0 2 0 1
D01 1 2 3 4
D02 4 0 5 6
D01 0 1 5 6
D03 1 6 4 1
D05 2 1 2 3
D06 8 5 9 7

I want to return the following values by using the formulas below:

D01 Per 2800 = SUMIF($A$3:$A$9;D01;INDEX($B$3:$E$9;0;MATCH(Per;$B$1:$E$1;0)*MATCH(2800;$B$2:$E$2;0))) Returns the sum: 1
D01 Lars 2900 = SUMIF($A$3:$A$9;D01;INDEX($B$3:$E$9;0;MATCH(Lars;$B$1:$E$1;0)*MATCH(2900;$B$2:$E$2;0))) Returns the sum: 11
D01 Per 2900 = SUMIF($A$3:$A$9;D01;INDEX($B$3:$E$9;0;MATCH(Per;$B$1:$E$1;0)*MATCH(2900;$B$2:$E$2;0))) Returns the sum: 5

What is wrong with this formula? Should I use another formula?

Please help :)
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,004
col Fcol J
perlarsperhenriette
2800290029003100
row 17codeper2800lars2900per2900henriette3100
DO10201
DO11234
DO24056
DO10156
DO31641
DO52123
DO6859789
per2800lars2900per2900henriette3100
DO115811
DO24056
DO31641
DO52123
DO6859789
formula giving 1 for DO1 / per2800
=SUMPRODUCT(($F$18:$F$24=$F31)*(G$18:G$24))

<colgroup><col span="7"><col><col span="3"></colgroup><tbody>
</tbody>
 

Klove86

New Member
Joined
Feb 18, 2016
Messages
3
Nice suggestion, but I need to filter on all 3 criteria afterwards.

The problems in my formula is that the first formula return is correct with 1, but number two formula should have returned 5 but returned 11 instead.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,502
Office Version
  1. 365
Platform
  1. Windows
number two formula should have returned 5 but returned 11 instead.

Hi, you could try this instead:

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="color: #333333;;">Per</td><td style=";">Lars</td><td style=";">Per</td><td style=";">Henriette</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="text-align: right;color: #333333;;">2800</td><td style="text-align: right;;">2900</td><td style="text-align: right;;">2900</td><td style="text-align: right;;">3100</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="color: #333333;;">D01</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="color: #333333;;">D01</td><td style="text-align: right;;">1</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;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="color: #333333;;">D02</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</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="color: #333333;;">D01</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</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="color: #333333;;">D03</td><td style="text-align: right;;">1</td><td style="text-align: right;;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</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="color: #333333;;">D05</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</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="color: #333333;;">D06</td><td style="text-align: right;;">8</td><td style="text-align: right;;">5</td><td style="text-align: right;;">9</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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 #BBB;border-top:none;text-align: center;background-color: #DAE7F5;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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">G3</th><td style="text-align:left">=SUMIF(<font color="Blue">$A$3:$A$9,"D01",INDEX(<font color="Red">$B$3:$E$9,0,MATCH(<font color="Green">1,INDEX(<font color="Purple">(<font color="Teal">$B$1:$E$1="Lars"</font>)*(<font color="Teal">$B$2:$E$2=2900</font>),0</font>),0</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

MARZIOTULLIO

Well-known Member
Joined
Aug 22, 2015
Messages
767

ADVERTISEMENT

A
B
C
D
E
F
G
H
I
J
1
PerLarsPerHenriette
2
2800​
2900​
2900​
3100​
D01Per
2800​
1​
3
D01
0​
2​
0​
1​
D01Lars
2900​
5​
4
D01
1​
2​
3​
4​
D01Per
2900​
8​
5
D02
4​
0​
5​
6​
6
D01
0​
1​
5​
6​
7
D03
1​
6​
4​
1​
8
D05
2​
1​
2​
3​
9
D06
8​
5​
9​
7​

<tbody>
</tbody>


Hi

J2 =SUM(IF($A$3:$A$9=G2,IF($B$1:$E$1=H2,IF($B$2:$E$2=I2,$B$3:$E$9)))) Control+Shift+Enter copy down
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,004
col Fcol J
1234
perlarsperhenriette
2800290029003100
row 17codeper2800lars2900per2900henriette3100
10201
11234
24056
10156
31641
52123
68597
col D
row 29code18
whoper
num2900
concatper2900
formula returning 8
=SUMPRODUCT((F18:F24=D29)*(OFFSET(F18,0,MATCH(D32,G17:J17,0)):OFFSET(F18,6,MATCH(D32,G17:J17,0))))

<colgroup><col span="9"><col><col span="7"></colgroup><tbody>
</tbody>
 

Klove86

New Member
Joined
Feb 18, 2016
Messages
3
A
B
C
D
E
F
G
H
I
J
1
PerLarsPerHenriette
2
2800​
2900​
2900​
3100​
D01Per
2800​
1​
3
D01
0​
2​
0​
1​
D01Lars
2900​
5​
4
D01
1​
2​
3​
4​
D01Per
2900​
8​
5
D02
4​
0​
5​
6​
6
D01
0​
1​
5​
6​
7
D03
1​
6​
4​
1​
8
D05
2​
1​
2​
3​
9
D06
8​
5​
9​
7​

<tbody>
</tbody>


Hi

J2 =SUM(IF($A$3:$A$9=G2,IF($B$1:$E$1=H2,IF($B$2:$E$2=I2,$B$3:$E$9)))) Control+Shift+Enter copy down

Thanks MARZIOTULLIO, that formula did the job perfekt. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

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
Top