Help with COUNTIF function for more than one criteria

[kwilson529]

I am trying to use the countif function for a column to determine how many cells are between 0-3, 4-7, 8 and above (for example)

I would like to use one countif function to be able to say =countif(t2:t256, [0-3] ) for each of the number ranges but don't know how to format it.

Can I just say =countif(t2:t256, >8) for over a value of 8?

I am working with Excel 2007.

 

[GlennUK]

Like this:

=COUNTIF(T2:T256,">=0")-COUNTIF(T2:T256,">=4")
=COUNTIF(T2:T256,">=4")-COUNTIF(T2:T256,">=8")
=COUNTIF(T2:T256,">=8")

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[AnotherHiggins]

I assume you're just dealing with integers? If not, you'll want to use some "=>"s instead of just ">"s.

=CountIf(A:A, ">0") - CountIf(A:A, ">3")

=CountIf(A:A, ">4") - CountIf(A:A, ">7")

=CountIf(A:A, ">7")

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[kwilson529]

Thank you both; one clarification -

if I write (">0" ... ">3") , it is not inclusive. If I write (">=0"... ">=3" is it then inclusive?

 

[AnotherHiggins]

Yes. But you wouldn't want to use ">=" EACH time. If you do, you'll "double count" the numbers being used.

That is, you might use:
[tab]=CountIf(A:A, ">0") - CountIf(A:A, "[!]>[highlight]=[/highlight]3[/!]")
but then you'd want to use:
[tab]=CountIf(A:A, "[!]>3[/!]") - CountIf(A:A, ">=7")

Is you were to use "[!]>[highlight]=[/highlight]3[/!]" in both formulas, you would count all instances of "3" in both formulas.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

0-3 is (>=0) - (>3)

4-7 is (>=4) - (>7)



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kwilson529]

WOW you guys are GREAT and FAST!

Thank you! I have finished some of the calculations and they're working like a dream.

Happy December!

 

[GlennUK]

John ( anotherhiggins ):

Yes. But you wouldn't want to use ">=" EACH time. If you do, you'll "double count" the numbers being used.

That is, you might use:
=CountIf(A:A, ">0") - CountIf(A:A, ">=3")
but then you'd want to use:
=CountIf(A:A, ">3") - CountIf(A:A, ">=7")

Is you were to use ">=3" in both formulas, you would count all instances of "3" in both formulas.

No, that's not right at all. The formulas are of the type
=IncludeThese - ExcludeThese
and your version exludes 3 both times, whereas mine does not "double count" ... it does it correctly.

Cheers, Glenn.

Beauty is in the eye of the beerholder.