calculate sum between different criteria

[Guest]

I need to sum a range of numbers based on 2 different criteria. Example:

Col A Col B
101 5
112 10
125 5
130 20

I need to sum Col B if the numbers in Col A are between 110 & 129. The
correct answer would be 15. I've tried IF, AND and nesting them but
nothing is working correctly

 

[Guest]

=SUMPRODUCT((A1:A10)>=110)*(A1:A10)<=129)*(B1:B10))

Change ranges to suit but cannot be whole columns i.e A:A is not valid

HTH

 

[Guest]

Sumproduct works, but I prefer something that more folks understand: add up
all the values where column A is greater than 110, then subtract from that
the sum of those where column A is greater than or equal to 129. What's left
is the sum of all those in between:
=SUMIF(A:A,">" & 110,B:B) - SUMIF(A:A,">=" & 129,B:B)
(In a nutshell, I use two sumifs to pick out a range on a single variable;
I'd resort to sumproduct to deal with criteria in multiple columns).

 

[Bob Phillips]

=SUMIF(A:A,">=110",B:B) - SUMIF(A:A,">129",B:B)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Once again you have saved me! Thanks so much!

=SUMIF(A:A,">=110",B:B) - SUMIF(A:A,">129",B:B)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)