Count how many times a number appears within a range of two numbers

**Maristar** · 04-02-2009, 01:04 AM

Hi,
I want to find how many entries are from a certain state (in Australia) e.g. how many people who are in victoria, which has a postcode range between 3000-3999. I have the people's postcode within my data.
I have looked through the forums and the solutions posted there didn't work for me!!
I have quite a big data set, so i would prefer not to do it by hand!
Thanks,
Marissa

**ratcat** · 04-02-2009, 01:25 AM

G'day and welcome to the formula

Try this,

=COUNTIF(B3:B11,{"<3999",">3000"})

Remember to adjust the range to suit your data layout

HTH

**Maristar** · 04-02-2009, 02:21 AM

this one doesn't seem to work...it returns a number almost double what it's supposed to be!

**ConneXionLost** · 04-02-2009, 02:37 AM

Try this one:

=SUMPRODUCT((B3:B11<=3999)*(B3:B11>=3000))

adjust the range as required, and use "Ctrl-Shift-Enter" once you've copied it to the formula bar.

HTH

**Domenic** · 04-02-2009, 10:56 AM

Here's another way... Assuming that B2:B100 contains the postal code, try...

=COUNTIF(B2:B100,">=3000")-COUNTIF(B2:B100,">3999")

Hope this helps!

xl-central.com