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.

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(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

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.

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

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