Công thức chung
=SUMPRODUCT(--ISERROR(range))
Tóm lược
Để đếm các ô có lỗi, bạn có thể sử dụng hàm ISERROR, được bao bọc trong hàm SUMPRODUCT. Trong ví dụ được hiển thị, ô E5 chứa công thức này:
=SUMPRODUCT(--ISERROR(B5:B9))
Giải trình
Hàm SUMPRODUCT chấp nhận một hoặc nhiều mảng, nhân các mảng với nhau và trả về "tổng tích" dưới dạng kết quả cuối cùng. Nếu chỉ cung cấp một mảng, hàm SUMPRODUCT chỉ trả về tổng các mục trong mảng.
Trong ví dụ được hiển thị, mục tiêu là đếm lỗi trong một phạm vi nhất định. Công thức trong E5 là:
=SUMPRODUCT(--ISERROR(B5:B9))
Làm việc từ trong ra ngoài, hàm ISERROR trả về TRUE khi một ô chứa lỗi và FALSE nếu không. Bởi vì có năm ô trong phạm vi B5: B9, ISERROR đánh giá từng ô và trả về năm kết quả trong một mảng như sau:
(TRUE;FALSE;TRUE;FALSE;TRUE)
Để ép buộc các giá trị TRUE / FALSE thành 1 và 0, chúng ta sử dụng dấu kép (-). Mảng kết quả trông như thế này:
(1;0;1;0;1)
Cuối cùng, hàm SUMPRODUCT tính tổng các mục trong mảng này và trả về tổng, trong trường hợp này là 3.
Tùy chọn ISERR
Hàm ISERROR đếm tất cả các lỗi. Nếu vì lý do nào đó bạn muốn đếm tất cả các lỗi ngoại trừ # N / A, bạn có thể sử dụng hàm ISERR để thay thế:
=SUMPRODUCT(--ISERR(B5:B9)) // returns 2
Vì một trong những lỗi được hiển thị trong ví dụ là # N / A, tùy chọn ISERR trả về 2 thay vì 3.
Công thức mảng với SUM
Bạn cũng có thể sử dụng hàm SUM để đếm lỗi, nhưng bạn phải nhập dưới dạng công thức mảng. Sau khi nhập công thức sẽ giống như sau:
(=SUM(--ISERROR(range)))
Các dấu ngoặc nhọn được Excel tự động thêm vào và chỉ ra một công thức mảng.
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter, ngoại trừ trong Excel 365.
