Originally Posted by EmzOLV

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, P5:P185 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.

Now for the cells containing a value of less than 5, I've used the following countif formula which works: =COUNTIF('RED WET 1'!P4:P185,"<=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'!P4:P185,"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'!P4:P185,"5.01")-COUNTIF('RED WET 1'!P4:P185,"<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, negative values. I have a feeling that the formulae I am using is calculating the first part, then deducting the second part, meaning that if there is a high number calculated by the second part, it is resulting in a minus and negative number. But I didn't think this would happen seeing as it was COUNTIF and these appeared to be identified ranges.

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

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


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