Counting nonblank and non white space cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a user who wants a simple count of nonblank cells. But he wants the
count to skip cells that have a space (or two or more) in them as well. My
formula is

=COUNTA(G91:OFFSET(G118,-2,0))

I can't seem to apply trim in there anywhere. How can I give him what he
has requested?
Thanks.
 
Hi!

Try this:

=SUMPRODUCT(--(LEN(A1:A5)>0),--(ISERROR(FIND(CHAR
(32),A1:A5))))

This will count all non-blank cells that do not have *ANY*
spaces, char(32), in them.

Biff
 
B1:

=COUNTIF(A1:A10,"?*")+COUNT(A1:A10)

C1:

=B1-(B1-SUMPRODUCT(--(LEN(TRIM(A1:A10))>0)))

C1 is the result cell.
 
LOL - That was my first thought, but I must have reread that question half a
dozen times before posting just because you had posted something different.
<vbg>
 
Thanks. It works!

Ken Wright said:
LOL - That was my first thought, but I must have reread that question half a
dozen times before posting just because you had posted something different.
<vbg>
Click to expand...
 
Back
Top