# sum range values of column depending on criteria

#### jumbledore

##### Active Member
I have the following table starting from cell A1 to D6:

 1 34 53 8 2 45 76 9 abc def ghi jkl 23 55 96 4 3 51 8 38 mno pqr stu vwx

<tbody>
</tbody>

In cell A9 if I were to enter a value say "stu" it should give me the sum of the 2 rows above. For example if I enter the formula in B9 it should return, 96+8=104. For "ghi", 53+76=129

How do I do so?
Thanks

Last edited:

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

#### 63falcondude

##### Well-known Member
How's this?

Excel 2010
ABCD
1134538
2245769
3abcdefghijkl
42355964
5351838
6mnopqrstuvwx
7
8CriteriaSum
9stu104
Sheet1
Cell Formulas
RangeFormula
B9=SUMPRODUCT((A3:D3=A9)*(A1:D2)+(A6:D6=A9)*(A4:D5))

#### BarryL

##### Well-known Member
very poor way of doing it is

=IFERROR(SUM(OFFSET(INDEX(6:6,1,MATCH(A11,6:6,0)),-2,0),OFFSET(INDEX(6:6,1,MATCH(A11,6:6,0)),-1,0)),SUM(OFFSET(INDEX(3:3,1,MATCH(A11,3:3,0)),-2,0),OFFSET(INDEX(3:3,1,MATCH(A11,3:3,0)),-1,0)))

Replies
4
Views
971
Replies
7
Views
311
Replies
6
Views
1K
Replies
2
Views
1K
Replies
3
Views
310

1,191,707
Messages
5,988,227
Members
440,139
Latest member
ngaicuong2017

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