Results 1 to 15 of 28

20100114, 14:17 #1
 Join Date
 Jan 2001
 Location
 Michigan, USA
 Posts
 408
 Thanks
 0
 Thanked 0 Times in 0 Posts
I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,">0")+countif(value(range,"<2" )))). I have something just not right because Excel doesn't like the formula. What am I not seeing?
TIA<font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

20100114, 14:17 #2
 Join Date
 Jan 2001
 Location
 Michigan, USA
 Posts
 408
 Thanks
 0
 Thanked 0 Times in 0 Posts
I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,">0")+countif(value(range,"<2" )))). I have something just not right because Excel doesn't like the formula. What am I not seeing?
TIA<font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

20100114, 14:17 #3
 Join Date
 Jan 2001
 Location
 Michigan, USA
 Posts
 408
 Thanks
 0
 Thanked 0 Times in 0 Posts
I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,">0")+countif(value(range,"<2" )))). I have something just not right because Excel doesn't like the formula. What am I not seeing?
TIA<font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

20100114, 14:17 #4
 Join Date
 Jan 2001
 Location
 Michigan, USA
 Posts
 408
 Thanks
 0
 Thanked 0 Times in 0 Posts
I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,">0")+countif(value(range,"<2" )))). I have something just not right because Excel doesn't like the formula. What am I not seeing?
TIA<font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

20100114, 14:17 #5
 Join Date
 Jan 2001
 Location
 Michigan, USA
 Posts
 408
 Thanks
 0
 Thanked 0 Times in 0 Posts
I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,">0")+countif(value(range,"<2" )))). I have something just not right because Excel doesn't like the formula. What am I not seeing?
TIA<font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

20100114, 14:17 #6
 Join Date
 Jan 2001
 Location
 Michigan, USA
 Posts
 408
 Thanks
 0
 Thanked 0 Times in 0 Posts
I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,">0")+countif(value(range,"<2" )))). I have something just not right because Excel doesn't like the formula. What am I not seeing?
TIA<font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

20100114, 14:17 #7
 Join Date
 Jan 2001
 Location
 Michigan, USA
 Posts
 408
 Thanks
 0
 Thanked 0 Times in 0 Posts
I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,">0")+countif(value(range,"<2" )))). I have something just not right because Excel doesn't like the formula. What am I not seeing?
TIA<font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

20100114, 14:17 #8
 Join Date
 Jan 2001
 Location
 Michigan, USA
 Posts
 408
 Thanks
 0
 Thanked 0 Times in 0 Posts
I am trying to count the number of cells in a column that contain the number 1. However, the cells being counted are actually formulas and if you PasteValues on the column most of the cells displaying a 1 are actually less than 1 or greater than one but less than 2. I think the formula needs to take the actual values into consideration. Something like, countif(value(range,">0")+countif(value(range,"<2" )))). I have something just not right because Excel doesn't like the formula. What am I not seeing?
TIA<font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

20100114, 14:27 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
How about this:
=COUNTIF(range,">0")COUNTIF(range,">=2")
Steve

20100114, 14:27 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
How about this:
=COUNTIF(range,">0")COUNTIF(range,">=2")
Steve

20100114, 14:27 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
How about this:
=COUNTIF(range,">0")COUNTIF(range,">=2")
Steve

20100114, 14:27 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
How about this:
=COUNTIF(range,">0")COUNTIF(range,">=2")
Steve

20100114, 14:27 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
How about this:
=COUNTIF(range,">0")COUNTIF(range,">=2")
Steve

20100114, 14:27 #14
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
How about this:
=COUNTIF(range,">0")COUNTIF(range,">=2")
Steve

20100114, 14:27 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
How about this:
=COUNTIF(range,">0")COUNTIF(range,">=2")
Steve