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

[Klove86]

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

 

[oldbrewer]

			col F				col J
				per	lars	per	henriette
				2800	2900	2900	3100
	row 17		code	per2800	lars2900	per2900	henriette3100
			DO1	0	2	0	1
			DO1	1	2	3	4
			DO2	4	0	5	6
			DO1	0	1	5	6
			DO3	1	6	4	1
			DO5	2	1	2	3
			DO6	8	5	9	7	89
				per2800	lars2900	per2900	henriette3100
			DO1	1	5	8	11
			DO2	4	0	5	6
			DO3	1	6	4	1
			DO5	2	1	2	3
			DO6	8	5	9	7	89
			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]

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]

number two formula should have returned 5 but returned 11 instead.

Hi, you could try this instead:

Excel 2013
	A	B	C	D	E	F	G
1		Per	Lars	Per	Henriette
2		2800	2900	2900	3100
3	D01	0	2	0	1		5
4	D01	1	2	3	4
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
Sheet1

Cell Formulas
Range	Formula
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]

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​		Per	Lars	Per	Henriette
2​		2800​	2900​	2900​	3100​		D01	Per	2800​	1​
3​	D01	0​	2​	0​	1​		D01	Lars	2900​	5​
4​	D01	1​	2​	3​	4​		D01	Per	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]

					col F				col J
						1	2	3	4
						per	lars	per	henriette
						2800	2900	2900	3100
			row 17		code	per2800	lars2900	per2900	henriette3100
					1	0	2	0	1
					1	1	2	3	4
					2	4	0	5	6
					1	0	1	5	6
					3	1	6	4	1
					5	2	1	2	3
					6	8	5	9	7
			col D
	row 29	code	1			8
		who	per
		num	2900
		concat	per2900
					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]

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​		Per	Lars	Per	Henriette
2​		2800​	2900​	2900​	3100​		D01	Per	2800​	1​
3​	D01	0​	2​	0​	1​		D01	Lars	2900​	5​
4​	D01	1​	2​	3​	4​		D01	Per	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.