how to count the number of #Div/0 value in excel

Question

I wish to count the number of " #Div/0" occurrence in excel, where 1/0 results in "#Div/0" value in excel, may I know anyone has experience how to count the number of it?

Thanks.

Answer 1

You can use the ERROR.TYPE function.

For example, the following array-formula, will count the number of #DIV/0! errors in the range D1:D10.

=SUM(IFERROR(IF(ERROR.TYPE(D1:D10)=2,1,0),0))

An array formula must be entered by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

Here is an example showing that #DIV/0, when entered as text, is not counted. Note the two changes in D9:D10. One was entered directly and became an error, the other was entered as a text string. Note also the formulas adjacent to D9:D10 indicating text vs error

Answer 2

Use COUNTIF. For example, if you wanted a count of #DIV/0! in the first 10 cells of column A you could use this:

=COUNTIF(A1:A10, "#DIV/0!")