COUNTIF Not Counting

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I have the following in the cells in column S:
s3 3 wks
s4 12 wks
s5 4 wks
s6 stk
s7 stk
s8 29 wks
s9 7 wks
s10 32 wks
and on.....
I'm trying to count the number of cells that are greater
than "26 wks". When I use Countif(S3:S82,">=26 wks") it
also counts the cells with 3 wks, 4 wks and 7 wks, then I
tried the formula like this =countif(value(left
(S3:S82,2)),">26 wks") and get an error (maybe because of
the space and "stk"). Is there any way to count just
those which are greater than 26 without getting 3, 4, 5,
6, 7 ... I even tried =countif(VALUE(LEFT(S3:S82,FIND
(" ",S3:S82)-1)),">=26"). I need to leave the text in the
cells and I'm sure that's part of the problem.

Any help would be greatly appreciated.
TIA
Joe
 
Try:

=SUM(N(IF(S3:S10<>"stk",SUBSTITUTE(TRIM(S3:S10)," wks",""))
*1>26))

Array-entered (meaning press ctrl/shift/enter for it to
work).

HTH
Jason
Atlanta, GA
 
=SUM(N(IF(S3:S10<>"stk",SUBSTITUTE(TRIM(S3:S10)," wks",""))*1>26))

Array-entered (meaning press ctrl/shift/enter for it to
work).
Click to expand...
...

Or an alternative,

=SUMPRODUCT(--(--SUBSTITUTE(SUBSTITUTE(S3:S10," wks",""),"stk","0")>26))

which requires fewer function calls (TRIM is unnecessary - Excel ignores leading
and trailing spaces when converting text representations of number to number
values, and the N function call is likely less efficient than two unary minus
operators), and doesn't have to be array-entered.

The better long-term solution for the OP would be to stop appending "wks" to the
number of weeks in column S, but instead consider the custom number format

0 "wks";"error";"stk";@

which would provide the same visual effect but make calculations much simpler.
 
Harlan Grove said:
...
..

Or an alternative,

=SUMPRODUCT(--(--SUBSTITUTE(SUBSTITUTE(S3:S10," wks",""),"stk","0")>26))

which requires fewer function calls (TRIM is unnecessary - Excel ignores leading
and trailing spaces when converting text representations of number to number
values, and the N function call is likely less efficient than two unary minus
operators), and doesn't have to be array-entered.

The better long-term solution for the OP would be to stop appending "wks" to the
number of weeks in column S, but instead consider the custom number format
Click to expand...


That's what I told the OP a week ago when he posted the same question..


Regards,

Peo Sjoblom
 
I added TRIM because it seemed like the OP was keying in
the entries, and this is susceptible to miskeying (like
adding an extra space on the end of the entry but of
course, it wouldn't be visible).

Yep, your formula seems a little more efficient than mine.

Jason
 
I added TRIM because it seemed like the OP was keying in
the entries, and this is susceptible to miskeying (like
adding an extra space on the end of the entry but of
course, it wouldn't be visible).
Click to expand...
...

=--" 12 " and =" 12 "*1 both return numeric 12. Add any number of
spaces to the beginning or end of a numeric string, and you'll find they have no
effect whatsoever on string to number conversions. So TRIM adds nothing but
processing time to string to number conversions. If there were spaces between
decimal digits or characters that can't appear within a valid numeric string,
TRIM again provides no benefits - the result of string to number conversion will
always be a #VALUE! error.

TRIM is often useful, sometimes necessary, but not for string to number
conversions.
 
...
...
That's what I told the OP a week ago when he posted the same question..
Click to expand...

And no doubt I read your response and subconsciously repeated it. As I've
written before, there's very little that's original in these newsgroups.
 
True. He did come up with an answer that was to reformat
the column and put weeks in the column title, but for
using a custom format to have it say "wks" in the cell I
don't recall that so all I would have is numbers. I still
had the problem of not counting correctly when the
letters "stk" were used. I tried using the custom format
which you suggested but when I enter a formula into the
cell that has been custom formated I get "stk" by itself
if the result is null. How can this be changed to a zero
or can it? I have tried many different combinations and
looked in help but can't figure it out.

Thanks for your responses, I have come up with some
original things and yes you have been very helpfull and
not wasted time.

Joe
 
Back
Top