Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 4
Smile 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]
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by EmzOLV View Post
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]

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")
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default 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.
  #5   Report Post  
Junior Member
 
Posts: 4
Smile

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

Quote:
Originally Posted by Spencer101 View Post
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")
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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
COUNTIF Function Provides Text Feedback, Not Values Michael S. Nelson Excel Worksheet Functions 4 May 3rd 09 06:24 AM
Excel graph 3 sets of data. 2 sets as lines 1 as column? AndyN Charts and Charting in Excel 2 July 11th 08 01:18 PM
Excel: Sumif function with two sets of criteria? Can it be done? Steelfan Excel Discussion (Misc queries) 2 May 7th 08 05:28 PM
How do I apply icon sets to percentage values in Excel worksheet? Still Learning? Excel Worksheet Functions 1 April 24th 08 12:14 AM
countif with two sets of non consectutive ranges at the same time Gover Excel Worksheet Functions 8 September 15th 07 06:13 AM


All times are GMT +1. The time now is 09:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"