countifs formula returns #div/0 error which cause a sum formula to return the same error

[geoffrey22]

I am using excel 2010 with windows 7

Each teacher has formulas similar to this: =
(COUNTIFS($B5:$B30,"x",D5:D30,">=70"))/(COUNTIFS($B5:$B30,"x",D5:D30,">=0"))*100
This formula is doing what it is supposed to do (identify students in a certain category and if they scored a 70 or higher).
The problem occurs when the teacher doesn't have any students in a specific category which then creates a divisor of 0, hence the error message.

1) How can I set this up so it returns N/A in the cell instead of #div/0

A second problem is that the data from each teacher's worksheet is pulled into another worksheet to create a summary page. The following is an example:
Smith 100
Jones 36
Max #div/0
Dill #div/0

Then when I use the following forumulas, I get #div/0 error message again.
=AVERAGE(Smit!hD39+Jones!D39+Max!D39+Dill!D39)/400
and
=SUM(B13:B16)/4

2) How can I get these two formulas to ignore the #div/0 message and make the calclulations?

 

[Special-K99]

You should check for the #DIV/0 error before attempting the calculation, ie

=IF(COUNTIFS(etc...)=0,"",COUNTIFS(etc...)/COUNTIFS(etc...))

 

[Jonmo1]

Welcome to the board.

You can use IFERROR

=IFERROR((COUNTIFS($B5:$B30,"x",D5:D30,">=70"))/(COUNTIFS($B5:$B30,"x",D5:D30,">=0"))*100,"N/A")