Count cells with numbers and ignore cells with errors
 

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.

Count cells with numbers and ignore cells with errors
 

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

--
Don Guillett
SalesAid Software

"WonderingaboutMicrosoft"
wrote in message
...
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.

Count cells with numbers and ignore cells with errors
 

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

WonderingaboutMicrosoft wrote:

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.

--

Dave Peterson

Count cells with numbers and ignore cells with errors
 

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"
wrote in message
...
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.

Count cells with numbers and ignore cells with errors
 

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


"WonderingaboutMicrosoft" wrote:

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.

Count cells with numbers and ignore cells with errors
 

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"),D2:D17)
=SUMPRODUCT(--(A2:A17="sam"),--(B2:B17="math"),--(C2:C17=1), D2:D17)
=SUMPRODUCT(--(A2:A17="sam"),--(B2:B17="math"),--(C2:C17=3), D2:D17)
=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"
wrote in message
...
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.

Count cells with numbers and ignore cells with errors
 

=COUNT( your_range )
???

--
Regards,
Luc.

"Festina Lente"


"WonderingaboutMicrosoft" wrote:

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.