[Webinar] Streamline your web hosting managementRegister Today

x
Solved

# MS Excel Function Syntax Help

Posted on 2001-08-10
Medium Priority
321 Views
Hello,

here is what I am attempting to do I will show the example first then ask the question:

Example:  Cell A1 is 5000
A2 is 7000
A3 is 7500
A4 is 8000
A5 is 10000
A6 is 15000
A7 is 20000

now using a function either Count or countif in Cell A10 what I am trying to do is Count the  cells that are between the range of 5000 and 10000.  Now I have tried this several times but either i get invalid or a 0 when I try to use the <> signs in my formulas.

for Example this formuala produces a 0.
=COUNTIF(C4:C10,"=>5000 =<10000")
now if I place just the =>5000 I get a value of 1.  But what I am trying to do is get it to count all of the cells between the two numbers.

Understand this is an example, My real data has over 400 entries and its not just some simple counting so I really need the function to work.
Any help would be appreciated.
0
Question by:Jeff_Keeler
• 2

LVL 17

Accepted Solution

calacuccia earned 800 total points
ID: 6372844
Hi Jeff,

This is indeed a limitation of the Countif function, you cannot set double conditions.

There are two alternatives:

Calculate both and look at the difference:

=COUNTIF(C4:C10,">=5000")-COUNTIF(C4:C10,">10000")

2/ The good one (but requiring array formula's
Use an array formula:

=SUM(IF(C4:C10>=5000,IF(C4:C10<=10000,1,0)))

Array formula are entered just as regular formula's, by typing them in the formula bar, but instead of hitting the <Enter> key to validate them, you need to press <CTRL> + <SHIFT> + <ENTER> together in that order.

If entered succesfully, you'll see accolades around the formula

{=SUM(IF(C4:C10>=5000,IF(C4:C10<=10000,1,0)))}
0

Author Comment

ID: 6372985
Thank you very very much.

0

LVL 17

Expert Comment

ID: 6373204
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
###### Suggested Courses
Course of the Month9 days, 5 hours left to enroll