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

=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

"JN" wrote:

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

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

"JN" wrote:

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

=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)



"JN" wrote in message
...
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

Once again you have saved me! Thanks so much!

"Bob Phillips" wrote:

=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)



"JN" wrote in message
...
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