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

[nadavrock]

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)

 

[nadavrock]

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

 

[Rick Rothstein]

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]

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]

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

M.

 

[Marcelo Branco]

Maybe

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

M.

 

[Rick Rothstein]

I think the result should be 160 - two last rows meet the criteria.
M.

I missed that last one!!!

Maybe

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

Yeah, that is probably what the OP is after.

 

[nadavrock]

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]

You are welcome. Thanks for the feedback.

M.