Count If Function
 

Is there a way to use the Count if function to count if a value is between 25
and 40?

Using Excel 2003

Count If Function
 

Do you mean =25 and <=40 ?

Try this:

=COUNTIF(A1:A20,"=25")-COUNTIF(A1:A20,"40")


--
Biff
Microsoft Excel MVP


"benny" wrote in message
...
Is there a way to use the Count if function to count if a value is between
25
and 40?

Using Excel 2003

Count If Function
 

What do you mean by "between"? If the 25 and 40 are to be excluded...

=COUNTIF(A1,"<40")-COUNTIF(A1,"<=25")

If they are to be included...

=COUNTIF(A1,"<=40")-COUNTIF(A1,"<25")

Note the different "<", "<=", "" and "=".

Rick


"benny" wrote in message
...
Is there a way to use the Count if function to count if a value is between
25
and 40?

Using Excel 2003

Count If Function
 

Assuming you are including 25 and 40

=SUM(COUNTIF(A1:A100,{"=25","40"})*{1,-1})


"benny" wrote:

Is there a way to use the Count if function to count if a value is between 25
and 40?

Using Excel 2003

Count If Function
 

To test only one cell:
=AND(COUNTIF(A1,{"=25","<=40"}))
or
=COUNTIF(A1,"=25")*COUNTIF(A1,"<=40")


To test a range of cells:
=SUM(COUNTIF(A1:A25,{"<25","<=40"})*{-1,1})
or
=COUNTIF(A1:A25,"<=40")-COUNTIF(A1:A25,"<25")

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"benny" wrote in message
...
Is there a way to use the Count if function to count if a value is between
25
and 40?

Using Excel 2003

Count If Function
 

For something that's a little more eccentric:

=INDEX(FREQUENCY(A1:A20,{25,40}-{1,0}),2)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Do you mean =25 and <=40 ?

Try this:

=COUNTIF(A1:A20,"=25")-COUNTIF(A1:A20,"40")


--
Biff
Microsoft Excel MVP


"benny" wrote in message
...
Is there a way to use the Count if function to count if a value is
between 25
and 40?

Using Excel 2003

Count If Function
 

"T. Valko" wrote in message
...
For something that's a little more eccentric:

=INDEX(FREQUENCY(A1:A20,{25,40}-{1,0}),2)

Very clever, well done.

--
Regards,

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


Replace @mailinator.com with @tiscali.co.uk

Count If Function
 

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

Rick Rothstein (MVP - VB) wrote:
What do you mean by "between"? If the 25 and 40 are to be excluded...

=COUNTIF(A1,"<40")-COUNTIF(A1,"<=25")

If they are to be included...

=COUNTIF(A1,"<=40")-COUNTIF(A1,"<25")

Note the different "<", "<=", "" and "=".

Rick


"benny" wrote in message
...

Is there a way to use the Count if function to count if a value is
between 25
and 40?

Using Excel 2003

Count If Function
 

"Sandy Mann" wrote in message
...
"T. Valko" wrote in message
...
For something that's a little more eccentric:

=INDEX(FREQUENCY(A1:A20,{25,40}-{1,0}),2)

Very clever, well done.

--
Regards,

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


Replace @mailinator.com with @tiscali.co.uk


I think it was one of Ron Coderre's posts where I first saw that technique.

--
Biff
Microsoft Excel MVP

Count If Function
 

I think it was one of Ron Coderre's posts where I first saw that
technique.

--
Biff
Microsoft Excel MVP

Well done Ron then.

I sometimes think that finding the origin of formulas is like when my kids
were small and one would start crying:

Me: "Why are you crying?"
1st Child: "Because she kicked me!"
Me: "Why did you kick her?"
2nd Child: "Becaue she hit me!"
Me: "Why did you hit her?"
1st Child: "Because she called me names"
Me: Why did ........ "

I never did get to the bottom of things. <g

--


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


Replace @mailinator.com with @tiscali.co.uk

Count If Function
 

Yes include the 25 and 40. All great solutions. I'm fixed now. Thanks so
much.

"benny" wrote:

Is there a way to use the Count if function to count if a value is between 25
and 40?

Using Excel 2003