Tip Printed from ExcelTip.com

---

Count the number of cells in a range containing all types of Errors in Microsoft Excel


Assume Range A1:B15

The Function to calculate number of cells containing all types of errors:
• {=SUM(IF(ISERROR(A1:B15),1))}

The Function to calculate number of cells containing error type #N/A:
• {=SUM(IF(ISNA(A1:B15),1))}

The Function to calculate number of cells containing error type #NULL!:
• {=SUM(IF(ISNA(ERROR.TYPE(A1:B15)),0,IF(ERROR.TYPE(A1:B15)=1,1,0)))}

The Function to calculate number of cells containing error type #DIV/0!:
• {=SUM(IF(ISNA(ERROR.TYPE(A1:B15)),0,IF(ERROR.TYPE(A1:B15)=2,1,0)))}

The Function to calculate number of cells containing error type #VALUE!:
• {=SUM(IF(ISNA(ERROR.TYPE(A1:B15)),0,IF(ERROR.TYPE(A1:B15)=3,1,0)))}

The Function to calculate number of cells containing error type #REF!:
• {=SUM(IF(ISNA(ERROR.TYPE(A1:B15)),0,IF(ERROR.TYPE(A1:B15)=4,1,0)))}

The Function to calculate number of cells containing error type #NAME?:
• {=SUM(IF(ISNA(ERROR.TYPE(A1:B15)),0,IF(ERROR.TYPE(A1:B15)=5,1,0)))}

The Function to calculate number of cells containing error type #NUM!:
• {=SUM(IF(ISNA(ERROR.TYPE(A1:B15)),0,IF(ERROR.TYPE(A1:B15)=6,1,0)))}

Note
The formulas are Array formulas.
After inserting a formula to cell, press F2 and then press Ctrl+Shift+Enter to change each formula to an Array formula.