Công thức chung
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Tóm lược
Để đếm các giá trị trong một danh sách bị thiếu trong danh sách khác, bạn có thể sử dụng công thức dựa trên các hàm COUNTIF và SUMPRODUCT.
Trong ví dụ được hiển thị, công thức trong H6 là:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Trả về 1 vì giá trị "Osborne" không xuất hiện trong B6: B11.
Giải trình
Hàm COUNTIF kiểm tra các giá trị trong một phạm vi dựa trên tiêu chí. Thông thường, chỉ một tiêu chí được cung cấp, nhưng trong trường hợp này, chúng tôi cung cấp nhiều hơn một tiêu chí.
Đối với phạm vi, chúng tôi cung cấp cho COUNTIF danh sách phạm vi được đặt tên1 (B6: B11) và đối với tiêu chí, chúng tôi cung cấp danh sách phạm vi được đặt tên2 (F6: F8).
Bởi vì chúng tôi cung cấp cho COUNTIF nhiều hơn một tiêu chí, chúng tôi nhận được nhiều hơn một kết quả trong một mảng kết quả giống như sau: (2; 1; 0)
Chúng tôi muốn chỉ đếm các giá trị bị thiếu, theo định nghĩa có tổng số là 0, vì vậy chúng tôi chuyển đổi các giá trị này thành TRUE và FALSE bằng câu lệnh "= 0", kết quả là: (FALSE; FALSE; TRUE)
Sau đó, chúng tôi buộc các giá trị TRUE FALSE thành 1s và 0s bằng toán tử âm kép (-), tạo ra: (0; 0; 1)
Cuối cùng, chúng tôi sử dụng SUMPRODUCT để cộng các mục trong mảng và trả về tổng số các giá trị bị thiếu.
Thay thế với MATCH
Nếu bạn thích các công thức theo nghĩa đen hơn, bạn có thể sử dụng công thức bên dưới, dựa trên MATCH, tính theo nghĩa đen các giá trị bị "thiếu" bằng cách sử dụng hàm ISNA:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
