Count number of occurences of values 1

[jsgs]

Hi everybody!

I have a range of values. I need to count how many occurences of the values 4, 5, and 6 there are in the range. I've used the following formula but it is not working.

=SUM(IF(AND($L$3:$L$36>=4, $L$3:$L$36<=6),1,0))

What's wrong with it?

 

[AnotherHiggins]

You're using SUM and you want to COUNT. They aren't the same thing.

You can use the CountIf function. If you're using 2007, there is a CountIf[highlight]s[/highlight] function available. Otherwise you can subtract one CountIf from another like this:
[tab]=CountIf(range, ">=4") - CountIf(range, ">6")

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

 

[AnotherHiggins]

Actually, looking at the formula in the OP, it looks like you're trying to use an array formula.

Array formulas must be entered using [Ctrl]+[Shift]+[Enter], not just enter. The formula you posted still won't work, but I just thought I'd mention that.

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

 

[jsgs]

Thanks for your help guys!

I've used the CountIfs and it's worked perfect.

 

[GSCaupling]

Didn't know about CountifS - thanks John!

GS

[Green]******^*******
[small]I[/small] [small]Hate[/small] [&hearts;] [small]Ambiguity.[/small][/green]