# how to get the sum of a range in which two specific if conditions apply

##### New Member
i have so far =SUMIFS(a2:b18, a2:b18, a2<b2)<b2)<b2)
i want to get the sum in column a. in which b is greater than a. and a is greater than 30. example:
a b
10 11
11 10
12 10
60 12
100 150
60 120

in this exmaple the sum should returns 110</b2)<b2)

Last edited:

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

##### New Member
wont let me edit. wannted to fix. what i have so far is =SUMIFS(a2:b18, a2:b18, a2<b2)

#### Rick Rothstein

##### MrExcel MVP
i have so far =SUMIFS(a2:b18, a2:b18, a2<b2)<b2)<b2)
i want to get the sum in column a. in which b is greater than a. and a is greater than 30. example:
a b
10 11
11 10
12 10
60 12
100 150
60 120

in this exmaple the sum should returns 110</b2)<b2)
Why is the answer 110... shouldn't it be 100? The 10 you from the first line violates your "and a is greater than 30" rule, so why are you counting it?

#### Rick Rothstein

##### MrExcel MVP
wont let me edit. wannted to fix. what i have so far is =SUMIFS(a2:b18, a2:b18, a2< b2)
Put a space after your < sign, otherwise this forum's interpreter thinks you started and HTML tag.

#### Marcelo Branco

##### MrExcel MVP
I think the result should be 160 - two last rows meet the criteria.

M.

#### Marcelo Branco

##### MrExcel MVP
Maybe

=SUMPRODUCT(--(B2:B7>A2:A7),--(A2:A7>30),A2:A7)

M.

#### Rick Rothstein

##### MrExcel MVP
I think the result should be 160 - two last rows meet the criteria.
M.
I missed that last one!!! :banghead:

Maybe

=SUMPRODUCT(--(B2:B7>A2:A7),--(A2:A7>30),A2:A7)
Yeah, that is probably what the OP is after.

##### New Member
yes marcelo that works. thank you. the idea of the example was indeed for the 2 last rows to meet cirteria. i miscalculated what sum that would give

#### Marcelo Branco

##### MrExcel MVP
You are welcome. Thanks for the feedback.

M.