Count cells with numbers and ignore cells with errors

[Guest]

I want to count the cells with numeric values and ignore the cells that
contain #DIV/0! in Office 2007 Beta BTR.

I have tried countif, countifs, and several other formulas. I have tried
setting the cells to TRUE and FALSE and counting numeric values but nothing
seems to work.

Thanks in advance.

 

[Don Guillett]

There are ways to easily do what you want but you should correct your
formulas so that they don't happen.

 

[Dave Peterson]

=COUNT(IF(ISNUMBER(A1:A99),A1:A99))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

In xl2007, you should be able to use the whole column. In earlier versions, you
couldn't.

 

[Bob Phillips]

You can simply use

=SUMPRODUCT(--ISNUMBER(A1:A20))

as the ISNUMBER ignores text and errors

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"WonderingaboutMicrosoft"

 

[Guest]

=SUMPRODUCT(--ISNUMBER(A1:A100))
or
=IF(ISNUMBER(A1:A8),A1:A8) ctrl+shift+enter (not just enter)

 

[SAM SEBAIHI]

You can use =IF(ISERROR(C6),0,C6) for every cell and move your table to
a cleaner cells without #DIV/0!

then use sumproduct instead of count:

For example :

add all sam's math tests (see below)
add all sam's math tests for semester 1 (see below)
add all sam's math tests for semester 3 (see below)
count all sam's math tests (see below)
count all sam's math tests for semester 1 (see below)
count all sam's math tests for semester 3 (see below)


=SUMPRODUCT(--(A2:A17="sam"),--(B2:B17="math"),D217)
=SUMPRODUCT(--(A2:A17="sam"),--(B2:B17="math"),--(C2:C17=1), D217)
=SUMPRODUCT(--(A2:A17="sam"),--(B2:B17="math"),--(C2:C17=3), D217)
=SUMPRODUCT((A2:A17="sam")*(B2:B17="math"))
=SUMPRODUCT((A2:A17="sam")*(B2:B17="math")*(C2:C17=1))
=SUMPRODUCT((A2:A17="sam")*(B2:B17="math")*(C2:C17=3))


"WonderingaboutMicrosoft"

 

[Guest]

=COUNT( your_range )
???