Count Functions

[Guest]

Hi Everyone i am looking for a formula that would let me count the number of
cells with a value between zero and 15? is there something that would work...

Thanks

 

[Guest]

Try this
=COUNTIF(A1:A10,"<=15")

 

[Alan]

One way,
=SUMPRODUCT(--(A1:A100>=0),--(A1:A100<16))
Regards,
Alan.

 

[JE McGimpsey]

One way:

=COUNTIF(A:A,">0") - COUNTIF(A:A, ">=15")

Another:

=SUMPRODUCT(--(A1:A1000>0),--(A1:A1000<15))

These assume "between" means non-inclusive...

 

[JE McGimpsey]

What if A1 = -1?

Try this
=COUNTIF(A1:A10,"<=15")

 

[JE McGimpsey]

Hmm... what if A1 = 15.5?

better: =SUMPRODUCT(--(A1:A100>=0),--(A1:A100<=15))

 

[Alan]

Better,
=SUMPRODUCT(--(A1:A100>=0),--(A1:A100<=15))
Regards,
Alan.

 

[Guest]

I guess I assumed we are working with positive numbers

 

[Alan]

I posted my second reply before I saw yours! You put me on the right track
with your original pointing out that a minus value would throw a spanner in
the works,
Regards,
Alan.

 

[Guest]

What If A1 Value = 0

One way:

=COUNTIF(A:A,">0") - COUNTIF(A:A, ">=15")

Another:

=SUMPRODUCT(--(A1:A1000>0),--(A1:A1000<15))

These assume "between" means non-inclusive...

 

[Alan Beban]

Hi Everyone i am looking for a formula that would let me count the number of
cells with a value between zero and 15? is there something that would work...

Thanks

Function CountBetw(iRange As range, lowNum, hiNum, Optional inclLow =
True, Optional inclHi = True)
If inclLow = True And inclHi = True Then
CountBetw = Application.CountIf(iRange, ">=" & lowNum) -
Application.CountIf(iRange, ">" & hiNum)
ElseIf inclLow = False And inclHi = False Then
CountBetw = Application.CountIf(iRange, ">" & lowNum) -
Application.CountIf(iRange, ">=" & hiNum)
ElseIf inclLow = True And inclHi = False Then
CountBetw = Application.CountIf(iRange, ">=" & lowNum) -
Application.CountIf(iRange, ">=" & hiNum)
ElseIf inclLow = False And inclHi = True Then
CountBetw = Application.CountIf(iRange, ">" & lowNum) -
Application.CountIf(iRange, ">" & hiNum)
End If
End Function

Alan Beban

 

[Guest]

If "between" 0 and 15 means non-inclusive 0 & 15

Then, one way,

=SUM(COUNTIF(A:A,{">0",">=15"})*{1,-1})

Another way,

=FREQUENCY(A1:A1000,{14,0})

Regards

 

[Alan]

Although I haven't tried that, I've no doubt it works, and I know of your
knowledge and skill over many years on these newsgroups, but with respect,
isn't that a sledgehammer to crack a walnut?
Regards,
Alan.

 

[Peo Sjoblom]

"These assume "between" means non-inclusive."

otherwise change the > to >= and the >= to >


--

Regards,

Peo Sjoblom

 

[Guest]

If "between" 0 and 15 means non-inclusive 0 & 15

Then, one way,

=SUM(COUNTIF(A:A,{">0",">=15"})*{1,-1})

Another way,

=FREQUENCY(A1:A1000,{14,0})

Regards

 

[T. Valko]

What if A1 is empty?

=SUMPRODUCT(--(ISNUMBER(A1:A100)),--(A1:A100>=0),--(A1:A100<=15))

Although COUNTIF is best.

 

[Rick Rothstein \(MVP - VB\)]

If "between" 0 and 15 means non-inclusive 0 & 15

Then, one way,

=SUM(COUNTIF(A:A,{">0",">=15"})*{1,-1})

Another way,

=FREQUENCY(A1:A1000,{14,0})

And even this CSE way...

=SUM(--(ABS(A1:A1000-7.5)<7.5))

CSE - Commit equation using Ctrl+Shift+Enter

Although I guess we could make this an Enter-normal formula by replacing SUM
with SUMPRODUCT.

Rick

 

[Guest]

Thanks Guys this one seemed to get the job done..... Comments from everyone
were very helpful

 

[Alan Beban]

Although I haven't tried that, I've no doubt it works, and I know of
your knowledge and skill over many years on these newsgroups, but with
respect, isn't that a sledgehammer to crack a walnut?
Regards,
Alan.

That's not my call. If you've got a handier nutcracker, by all means
ignore my post. But once it's coded and in my library, and after
reviewing the responses in this thread, I personally don't see much of
anything easier, nor more readily committed to memory, than typing in a cell

=CountBetw(a1:a100,0,15) if inclusive or
=CountBetw(a1:a100,0,15,false,false) if exclusive.

And the frequency with which the question arises in this newsgroup, and
the qualification that always comes up in the responses about
inclusive/not inclusive, suggested to me that something more intuitive
and more obvious than, e.g.,

=SUMPRODUCT(--(ISNUMBER(A1:A100)),--(A1:A100>=0),--(A1:A100<=15))

might be useful.

Also, with respect, coding such a solution allows one to think through
the problem and avoid posting things like

=SUMPRODUCT(--(A1:A13>=0),--(A1:A13<=15)), which, as was pointed out,
counts empty cells as having values between 0 and 15; or

=SUMPRODUCT(--(A1:A100>=0),--(A1:A100<16)), which has the empty cells
problem and also, which was also pointed out, doesn't allow for numbers
between 15 and 16.

But to each his own.

Alan Beban

 

[Sandy Mann]

=FREQUENCY(A1:A1000,{14,0})

These NG's never cease to amaze me!

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk