I need a fx for counting numbers between numbers (between 18 - 59

J

Jen

I am trying to have excel count numbers that fall between certain numbers.
I.e. all numbers in column B that range from 18-59 (that include the #18 and
# 59). I have tried the COUNTIF fx but can only do ">=18" and "<=59" This
ends up counting all numbers above 18 and all numbers below 59. I tried
BETWEEN 18 AND 59 but I obvoiusly don't know the appropiate language. Thanks
in advance. Jen
 
T

T. Valko

Try this:

=COUNTIF(B:B,">=18")-COUNTIF(B:B,">59")

Better to use cells to hold the boundary values:

D1 = 18
E1 = 59

=COUNTIF(B:B,">="&D1)-COUNTIF(B:B,">"&E1)
 
S

Shane Devenshire

Hi,

Personally I like =COUNTBETWEEN(18,59) Just kidding, I've asked Microsoft
for a between function but no luck yet.

In 2003:

=SUMPRODUCT(B1:B100>=18)*(B1:B100<=59))

In 2007:

=COUNTIFS(B1:B100,">=18",B1:B100,"<=59")

or you can replace the 18 and 59 with cell references, suppose A1 and A2

=COUNTIFS(B1:B100,">="&A1,B1:B100,"<="&A2)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top