Archive of Mr Excel Message Board
Back to Forms in Excel VBA archive index
Back to archive home
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?
| Check out our Excel Resources
|
 |
 |
Posted by Juan Pablo G. on January 24, 2002 6:42 AM
Thanks, now I'm having trouble getting a sum if the conditions are
Posted by Lori on January 24, 2002 7:45 AM
Re: Thanks, now I'm having trouble getting a sum if the conditions are
Posted by Lori on January 24, 2002 7:48 AM
Now I want to get a sum if the values in my first column are greater than 700, but less than 799.
Thanks again.
Specifically, I want to sum if the conditions are between!
Posted by Lori on January 24, 2002 7:51 AM
Re: Specifically, I want to sum if the conditions are between!
Posted by Aladin Akyurek on January 24, 2002 7:58 AM
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.
Re: Specifically, I want to sum if the conditions are between!
Posted by Russell Hauf on January 24, 2002 8:11 AM
Try this:
=SUMIF(A2:A210,">=799")-SUMIF(A2:A210,">700")
Hope it helps,
Russell
These are not doing what I need. Help!
Posted by Lori on January 24, 2002 8:32 AM
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?
Re: These are not doing what I need. Help!
Posted by Juan Pablo G. on January 24, 2002 8:38 AM
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.
Re: These are not doing what I need. Help!
Posted by Russell Hauf on January 24, 2002 8:44 AM
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
Got it! Thank you so much!
Posted by Lori on January 24, 2002 8:47 AM
So, I get nothing :( [Just Kidding.] (Re: Got it! Thank you so much!)
Posted by Aladin Akyurek on January 24, 2002 8:52 AM
You're too late ! [NT]
Posted by Juan Pablo G. on January 24, 2002 8:54 AM
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our
online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.