If and blanks

L

LucasBuck

Is using ifnumber the best way to have a formula not count blank cells
as zeros?
If so, can someone show me using =COUNT(C3:C31)
 
S

swatsp0p

Without you telling us what data (or blanks or zeros) are in your
specified range, we can't understand what you are looking for. COUNT,
by definition, only counts cells containing numbers (from the HELP
file: -Counts the number of cells that contain numbers -).

Your formula, as written should do what you ask. Also, I am not
familiar with "ifnumber". Is that a UDF?
 
L

LucasBuck

Sorry, I'll be more specifc (the ifnumber was just something I saw while
searching)

I I did get part of it worked out. I had asked earlier how to count
pairs of zeros in a list with 2 columns (in otherwords, two zeroes in
the same row) Someone suggested
=SUMPRODUCT(--(B1:B31=0),--(C1:C31=0))
The problem is, it counts blanks as zeroes. Everything is set up so
items can be added later, so it's going to have blanks unless I adjust
it every time.
 
S

swatsp0p

simply add one more condition to the formula, as such:

=SUMPRODUCT(--(B1:B31=0),--(C1:C31=0),--(B1:B31<>""))

HTH

Bruce
 
R

Roger Govier

Hi Lucas

I was one of the people who gave you that solution, but I did not consider
the case of blank cells.
Bob Phillips had thought about the problem more deeply, and also posted a
solution with another condition added which addressed the value of blank cells.

I case you missed his posting
=SUMPRODUCT(--(A1:A10=0),--(B1:B10=0),--(A1:A10<>""))

or for your ranges
=SUMPRODUCT(--(B1:B31=0),--(C1:C31=0),--(B1:B31<>""))


Regards

Roger Govier
 
L

LucasBuck

Thank you both very much. I didn't notice the addition in the othe
post. Very much appriciated. (Trying to finish something for you
principal when you have 29 kids in the room can lead to distraction.
:
 
Top