calculate sum between different criteria
 

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

calculate sum between different criteria
 

=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

calculate sum between different criteria
 

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

calculate sum between different criteria
 

=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

calculate sum between different criteria
 

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