Excel Countif Function - Calculating Between Two Sets of Values
 

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]

Excel Countif Function - Calculating Between Two Sets of Values
 

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'!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")

try:
=COUNTIF(P4:P185,"<=10")-COUNTIF(P4:P185,"<=5")
or:
=SUMPRODUCT(--(P4:P1855),--(P4:P185<=10))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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 appropriate numbers for each distance range.
You could put these in cells and reference them in the formula so you only have to write the formula once and then copy it. But without seeing your workbook I couldn't tell you exactly what the formula would look like then.

=SUMPRODUCT(('RED WET 1'!P4:P1855)*('RED WET 1'!P4:P185<=10))

=COUNTIFS('RED WET 1'!P4:P185,"5",'RED WET 1'!P4:P185,"<=10")

Excel Countif Function - Calculating Between Two Sets of Values
 

On Tue, 19 Mar 2013 17:22:21 +0000, EmzOLV wrote:


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.


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

=FREQUENCY(P5:P185,{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.

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

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

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