If and blanks

[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)

[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?

[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.

[swatsp0p]

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

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

HTH

Bruce

[Bob Phillips]

Read my reply in your previous post, I specifically addressed that issue.

--
HTH

Bob Phillips

"LucasBuck" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> LucasBuck
> ------------------------------------------------------------------------
> LucasBuck's Profile:
http://www.excelforum.com/member.php...o&userid=19710
> View this thread: http://www.excelforum.com/showthread...hreadid=467238
>

[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


LucasBuck wrote:
> 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.
>
>

[LucasBuck]

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