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

#### Klove86

##### New Member
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?

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### oldbrewer

##### Well-known Member
 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

##### New Member
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
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
 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

##### Well-known Member
 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

##### New Member
 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.

Replies
3
Views
237
Replies
8
Views
231
Replies
0
Views
293
Replies
3
Views
440
Replies
11
Views
305

1,196,017
Messages
6,012,867
Members
441,737
Latest member
bijayche

### 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.

### Which adblocker are you using?

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

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