Công thức chung
=SUMPRODUCT(--NOT(ISERROR(range)))
Tóm lược
Để đếm số ô không có lỗi, bạn có thể sử dụng hàm ISERROR và NOT, được bao bọc trong hàm SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong E5 là:
=SUMPRODUCT(--NOT(ISERROR(B5:B14)))
Giải trình
Trong ví dụ này, mục tiêu là đếm số ô trong một phạm vi không chứa lỗi.
Làm việc từ trong ra ngoài, trước tiên chúng tôi sử dụng hàm ISERROR trên toàn bộ phạm vi:
ISERROR(B5:B14) // check all 10 cells
Vì có mười ô trong phạm vi B5: B14, ISERROR trả về một mảng có mười kết quả như sau:
(FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Ở đây, mỗi giá trị TRUE chỉ ra một giá trị ô là lỗi. Vì mục đích là đếm các ô không có lỗi, chúng tôi đảo ngược các kết quả này bằng hàm NOT:
NOT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE))
trả về:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Lưu ý rằng mỗi giá trị TRUE bây giờ tương ứng với một ô không chứa lỗi. Mảng này hiện ở định dạng đúng - Giá trị TRUE cho biết ô không có lỗi, giá trị FALSE cho biết ô có lỗi.
Vì SUMPRODUCT chỉ hoạt động với dữ liệu số, bước tiếp theo là chuyển đổi các giá trị TRUE và FALSE thành các giá trị số tương đương của chúng, 1 và 0. Chúng tôi thực hiện điều này với một âm kép (-):
--(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Mảng kết quả trông như thế này:
(1;0;1;1;1;0;1;1;1;0)
Cuối cùng, SUMPRODUCT tính tổng các mục trong mảng này và trả về tổng số, trong ví dụ này là số 3:
=SUMPRODUCT((1;0;1;1;1;0;1;1;1;0)) // returns 7
Hàm ISERR
Giống như hàm ISERROR, hàm ISERR trả về TRUE khi một giá trị là lỗi. Sự khác biệt là ISERR bỏ qua lỗi # N / A. Nếu bạn muốn đếm các ô không chứa lỗi và bỏ qua lỗi # N / A, bạn có thể thay ISERR cho ISERROR:
=SUMPRODUCT(--NOT(ISERR(B5:B14))) // ignore #N/A
Tùy chọn SUM
Bạn cũng có thể sử dụng hàm SUM để đếm lỗi. Cấu trúc của công thức giống nhau:
=SUM(--NOT(ISERROR(B5:B14)))
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.
