calculate sum between different criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
=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
 
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).
 
=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)
 
Once again you have saved me! Thanks so much!

Bob Phillips said:
=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)
Click to expand...
 
Back
Top