Count If Function

B

benny

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

Using Excel 2003
 
T

T. Valko

Do you mean >=25 and <=40 ?

Try this:

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

Rick Rothstein \(MVP - VB\)

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
 
T

Teethless mama

Assuming you are including 25 and 40

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

Ron Coderre

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)
 
T

T. Valko

For something that's a little more eccentric:

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

Sandy Mann

T. Valko said:
For something that's a little more eccentric:

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

Very clever, well done.

--
Regards,

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
A

Alan Beban

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
 
T

T. Valko

Sandy Mann said:
Very clever, well done.

--
Regards,

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
Click to expand...

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

Sandy Mann

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

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top