Need Excel syntax for BETWEEN 2

[mhoyt]

I have a range called SCORES, with numeric values 0 to 100.

I want to see how many scores are between (inclusive) let's say 70 and 79. And basically every other range of possibilities in 10 point increments. So I thought I'd use column D for the minimum point of any given range, and column E for the maximum. So D10 = 70 and E10 = 79.

Then I wanted a formula in F10 like COUNTIF (D10<=SCORES<=F10),
but this does not work. I know I could kludge something like count all scores greater than the minimum and then subtract the values of all higher rows, but a nice BETWEEN syntax would make me happier. Any ideas?

TIA Mike

 

[SkipVought]

Hi,
[tt]
sumproduct((SCORES<=F10)*(SCORES>=D10))
[/tt]


Skip,
[sub]
[red][/red]
[/sub]

 

[mhoyt]

Bingo! Thanks very much Skip!

 

[AnotherHiggins]

Or
[tab][COLOR=blue white]=COUNTIF(Scores,"<"&$E$10)-COUNTIF(Scores,"<"&$D$10)
[/color]

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

Help us help you. Please read FAQ181-2886 before posting.

 

[mhoyt]

Hey, another neat idea! Thanks.

I see you are concatanating the < sign to make it work, I wasn't haven't ay luck without the concatanation, so thats another good tip.

Skip, why does yours work? What does the sumproduct function do?

 

[AnotherHiggins]

Glad to help, Mike.

For a good, if somewhat lengthy, explanation of SumProduct, see:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

Help us help you. Please read FAQ181-2886 before posting.

 

[mhoyt]

Holy cow! That is a great link John! I love days where I learn new skills, this is a banner day!

 

[SkipVought]

...says it all!

Skip,
[sub]
[red][/red]
[/sub]

 

[AnotherHiggins]

Happy to help!

Have a great weekend!

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

Help us help you. Please read FAQ181-2886 before posting.