Công thức Excel: Đếm các ô có lỗi
Công thức Excel: Đếm các ô có lỗi

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 BÁC SĨ chức năng, được 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

Các 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 các sản phẩm” 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 ô có lỗi và FALSE nếu không. 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ư thế này:

{TRUE;FALSE;TRUE;FALSE;TRUE}

Để ép buộc các giá trị TRUE / FALSE thành 1 và 0, chúng tôi sử dụng Âm kép (-). Mảng kết quả trông như thế này:

{1;0;1;0;1}

Cuối cùng, 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ừ Excel 365.

https://exceljet.net/formula/count-cells-that-contain-errors