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
Medium Priority
309 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
[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

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:

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

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

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…