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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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,690
Office Version
  1. 365
Platform
  1. Windows
number two formula should have returned 5 but returned 11 instead.

Hi, you could try this instead:


Excel 2013
ABCDEFG
1PerLarsPerHenriette
22800290029003100
3D0102015
4D011234
5D024056
6D010156
7D031641
8D052123
9D068597
Sheet1
Cell Formulas
RangeFormula
G3=SUMIF($A$3:$A$9,"D01",INDEX($B$3:$E$9,0,MATCH(1,INDEX(($B$1:$E$1="Lars")*($B$2:$E$2=2900),0),0)))
 

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,005
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. :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,837
Messages
5,833,918
Members
430,244
Latest member
Ireland1

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