Is there a way to enter a formula to count values that are between 700 and 799
Posted by Lori on January 24, 2002 6:40 AM
It seems like I want countif a2:a210,">700" but I don't know how (if?) to combine that with: and,"<799".
Any way to do this?
Posted by Juan Pablo G. on January 24, 2002 6:42 AM
See 17173.html [NT]
Posted by Lori on January 24, 2002 7:45 AM
Thanks, now I'm having trouble getting a sum if the conditions are
Posted by Lori on January 24, 2002 7:48 AM
Re: Thanks, now I'm having trouble getting a sum if the conditions are
Now I want to get a sum if the values in my first column are greater than 700, but less than 799.
Thanks again.
Posted by Lori on January 24, 2002 7:51 AM
Specifically, I want to sum if the conditions are between!
Posted by Aladin Akyurek on January 24, 2002 7:58 AM
Re: Specifically, I want to sum if the conditions are between!
Lets say that the relevant ranges are A2:A210 and B2:B210.
Lets put 700 in, say, E1 and 799 in E2.
You apparently have an exclusive between condition. If so,
=SUMIF(A2:A210,">"&E1,B2:B210)-SUMIF(A2:A210,">="&E2,B2:B210)
will do what you want.
Posted by Russell Hauf on January 24, 2002 8:11 AM
Re: Specifically, I want to sum if the conditions are between!
Try this:
=SUMIF(A2:A210,">=799")-SUMIF(A2:A210,">700")
Hope it helps,
Russell
Posted by Lori on January 24, 2002 8:32 AM
These are not doing what I need. Help!
When I use these formulas, its summing everything in column a that is less than 799, and everything over 700. What I need is only what falls between 700 and 700. Is this possible?
Posted by Juan Pablo G. on January 24, 2002 8:38 AM
Re: These are not doing what I need. Help!
Let's try an example. In A1:A10 i have this:
700
750
800
710
600
730
500
720
900
100
In here, there are 4 cells that meet the criteria (>700 and < 799)
The formulas are doing this:
=SUMIF(A2:A210,">700")
returns 4610
the second one
=SUMIF(A2:A210,">=799")
returns 1700
Therefor, 4610 - 1700 = 2910 which IS THE SAME as (750 + 710 + 730 + 720).
Make sure you're putting the formulas right.
Juan Pablo G.
Posted by Russell Hauf on January 24, 2002 8:44 AM
Re: These are not doing what I need. Help!
In your post above you said, "Now I want to get a sum if the values in my first column are greater than 700, but less than 799. "
If you want I'm assuming that you didn't really mean "between 700 and 700" below. Maybe 700 and 800? If so, you can use what Aladin wrote. Here's how it works:
=SUMIF(A2:A210,">800")
gives you the sum of all numbers above 800 (so 801 and up - if you do not want 800 in your "between 700 and 800" then change 800 to 799 in the above formula).
Now, another part of the formula was:
=SUMIF(A2:A210,">=700")
This gives you the sum of all numbers in your list greater than or equal to 700 (including the ones that are greater than 800!). So, if you subtract the sum of the numbers greater than 800 from the sum of all numbers greater than or equal to 700, what do you get? That's right, the sum of the numbers between 700 and 800! So, your final formula is:
=SUMIF(A2:A210,">=700")-SUMIF(A2:A210,">800")
Clear as mud? Hopefully much clearer,
Russell
Posted by Lori on January 24, 2002 8:47 AM
Got it! Thank you so much!
Posted by Aladin Akyurek on January 24, 2002 8:52 AM
So, I get nothing :( [Just Kidding.] (Re: Got it! Thank you so much!)
Posted by Juan Pablo G. on January 24, 2002 8:54 AM
You're too late ! [NT]
