[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

MS Excel Function Syntax Help

Posted on 2001-08-10
3
Medium Priority
?
321 Views
Last Modified: 2012-06-21
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
Comment
Question by:Jeff_Keeler
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
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:

1/ The bad one
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

by:Jeff_Keeler
ID: 6372985
Thank you very very much.

0
 
LVL 17

Expert Comment

by:calacuccia
ID: 6373204
GlAd to help you, Jeff.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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.

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question