Count If Function

[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. Valko]

Do you mean >=25 and <=40 ?

Try this:

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

 

[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

 

[Teethless mama]

Assuming you are including 25 and 40

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

 

[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. Valko]

For something that's a little more eccentric:

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

 

[Sandy Mann]

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

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

 

[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. Valko]

Very clever, well done.

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

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

 

[Sandy Mann]

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

technique.

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

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

 

[benny]

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