Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

MS Excel Function Syntax Help

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
Jeff_Keeler
Asked:
Jeff_Keeler
  • 2
1 Solution
 
calacucciaCommented:
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
 
Jeff_KeelerAuthor Commented:
Thank you very very much.

0
 
calacucciaCommented:
GlAd to help you, Jeff.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now