Excel Countif Function - Calculating Between Two Sets of Values

[EmzOLV]

Hey guys! Hope you can help me - I've been struggling trying to work ou
what is happening with some of my calculations.

Here is my scenario:
I am trying to identify, between a range of cells (in this case, P518
on the separate worksheet RED WET 1), how many of these contain a valu
less than 5, a value between 5 and 10, a value between 10 and 20, an
upwards up until 100, whereby I then want to work out how many cell
contain a value of 100 or more.

Now for the cells containing a value of less than 5, I've used th
following countif formula which works: =COUNTIF('RED WE
1'!P4185,"<=5.00")

For the cells containing a value of 100 or more, I've used the followin
countif formula which works: =COUNTIF('RED WET 1'!P4185,">100.01")

Now, the inbetween values are where I am struggling.

For the cells containing a value which lies between 5.01 and 10, I hav
this formula: =COUNTIF('RED WET 1'!P4185,">5.01")-COUNTIF('RED WE
1'!P4185,"<10.00")

And I used the same formula, but as the value I wanted to find changed
I changed (so instead of ">5.01" it would have ">10.01" or ">20.01").

BUT what has started to happen is that it is returning to me, negativ
values. I have a feeling that the formulae I am using is calculating th
first part, then deducting the second part, meaning that if there is
high number calculated by the second part, it is resulting in a minu
and negative number. But I didn't think this would happen seeing as i
was COUNTIF and these appeared to be identified ranges.

So basically - I tried the different sums - so / and + and - and * bu
these still just seemed to have different effects and never returned th
true number of how many cells contained say, a value greater than 5.0
and less than 10.00.

Can someone please help me? I've tried everything from COUNTIF t
SUMPRODUCT, and a few other internet suggestions, and I'm beginning t
feel like giving up. Surely this can't be as hard as I'm trying to fin
out? This seemed relatively simple and something that COUNTIF should b
able to do, but perhaps I'm just not doing it right?


[Background info - the cells contain a distance from a postcode to tha
location, such as 10.11 miles away, 11.01 miles away, and so I am tryin
to work out how many cells contain values between certain amounts s
that I can then do nifty little charts of how many people live a certai
number of miles away from this certain point

 

[Claus Busch]

Hi,

Am Tue, 19 Mar 2013 17:22:21 +0000 schrieb EmzOLV:

Now for the cells containing a value of less than 5, I've used the
following countif formula which works: =COUNTIF('RED WET
1'!P4185,"<=5.00")

For the cells containing a value of 100 or more, I've used the following
countif formula which works: =COUNTIF('RED WET 1'!P4185,">100.01")

Now, the inbetween values are where I am struggling.

For the cells containing a value which lies between 5.01 and 10, I have
this formula: =COUNTIF('RED WET 1'!P4185,">5.01")-COUNTIF('RED WET
1'!P4185,"<10.00")

try:
=COUNTIF(P4185,"<=10")-COUNTIF(P4185,"<=5")
or:
=SUMPRODUCT(--(P4185>5),--(P4185<=10))


Regards
Claus Busch

 

[Ron Rosenfeld]

Hey guys! Hope you can help me - I've been struggling trying to work out
what is happening with some of my calculations.

Here is my scenario:
I am trying to identify, between a range of cells (in this case, P5185
on the separate worksheet RED WET 1), how many of these contain a value
less than 5, a value between 5 and 10, a value between 10 and 20, and
upwards up until 100, whereby I then want to work out how many cells
contain a value of 100 or more.

For a problem like this, I would use the FREQUENCY function.

=FREQUENCY(P5185,{5,10,20,100})

Enter it as an array from, for example A1:A5
As written,
A1: will show the count of values less than or equal to five.
A2: will show the count of values greater than five but less than or equal to ten
A3: will show the count of values greater than ten but less than or equal to 20.
A4: will show the count of values greater than 20 but less than or equal to 100
A5: will show the count of values greater than 100

To enter the formula, first select A1:A5. Enter the formula into the formula bar, then, instead of hitting <ENTER>, hold down <CTRL+SHIFT> while hitting <ENTER>. Excel will copy the same formula into A1:A5 and place curly brackets {...} around the formula.

 

[EmzOLV]

Firstly - THANKS everyone who replied I really appreciate the tim
you took to help me with my issue!

You can use the SUMPRODUCT formula below in any Excel version.
The COUNTIFS one will only work in Excel 2007 or later.

You just need to change the 5 & 10 in these formulas for appropriat
numbers for each distance range.
You could put these in cells and reference them in the formula so yo
only have to write the formula once and then copy it. But withou
seeing your workbook I couldn't tell you exactly what the formula woul
look like then.

=SUMPRODUCT(('RED WET 1'!P4185>5)*('RED WET 1'!P4185<=10))

=COUNTIFS('RED WET 1'!P4185,">5",'RED WET 1'!P4185,"<=10")

The SUMPRODUCT code here works perfectly and I've managed to cross-chec
the result in gathering up the sum of all the results and doing a quic
few spot checks to see if they are adding up correctly. This is exactl
what I was needing. Thank you!

I would have put up my spreadsheet but aside from the mileage aspect,
lot of it was confidential but thats perfect thank you