Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Excel Function Syntax Help

Posted on 2001-08-10
3
Medium Priority
?
309 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

722 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