countif clarification

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

Guest

Thank you for earlier response... have the formula now to count blank cells.

Been perusing your other answers but now have this question...

Scenario: one column, c4:c34. Will contain some numbers, some zeros, and
some necessary blank rows in case I add more information (either zero's or
numbers). Realized I didn't need to count the blank rows, just the zeros and
the numbers.

I can now count all the cells with zero's. How do I count the cells that
are greater than zero? I've tried a variety of formulas but my guess'n golly
hasn't eureka'd yet!

Linda
 
Yes, I've tried that and other variations from the discussion group answers
that seemed logical... well for the questions posed they were...

Anyway...That one you show returns a value of "0". In that column of 31
cells, two contain numbers, 3 contain zeros, and the rest are blank. I can
count the zero's, I can count the blanks, but counting the numbered cells is
..... well .... I'm drawing a blank.
 
Sounds like your numbers are being stored as text. If this returns a value

=SUMPRODUCT(--(C4:C34>0))

that will confirm it.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Sorry, that formula returned a value of 5 which is counting the 2 cells with
numbers plus the 3 cells with zeros.
Not the right one yet.... need it to return a value of 2, and then that'll
probably be the right formula. What could be simpler than counting anything
greater than 0? The answer's here somewhere.
Linda
 
Oh, and I reconfirmed that the row of cells c4:c34 are formatted as number,
not text. No difference in the result.
 
It wasn't meant to be an alternative, just confirmed they are text, which it
did. Change them to General and F2 the text cells.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
It doesn't matter if they are formatted as numbers, excel sees them as text,
no formatting will change that, you will need to calculate them if they
don't have trailing or leading spaces..

Try by copying an empty cell, select the range and do edit>paste special and
select add..

then use the countif function

If that doesn't work you have invisible characters like html char(160) or
spaces in the cells


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
Back
Top