Công thức chung
=SUMPRODUCT((rng1=crit1)*ISNA(MATCH(rng2,crit2,0)))
Tóm lược
Để đếm với nhiều tiêu chí, bao gồm logic cho KHÔNG một trong số những thứ, bạn có thể sử dụng hàm SUMPRODUCT cùng với các hàm MATCH và ISNA.
Trong ví dụ được hiển thị, công thức trong G8 là:
=SUMPRODUCT((gender=F4)*ISNA(MATCH(group,G4:G5,0)))
Trong đó "giới tính" là phạm vi được đặt tên C4: C12 và "nhóm" là phạm vi được đặt tên D4: D12.
Lưu ý: MATCH và ISNA cho phép công thức mở rộng quy mô dễ dàng để xử lý nhiều loại trừ hơn, vì bạn có thể dễ dàng mở rộng phạm vi để bao gồm các giá trị "KHÔNG" bổ sung.
Giải trình
Biểu thức đầu tiên bên trong SUMPRODUCTS kiểm tra các giá trị trong cột C, Giới tính, so với giá trị trong F4, "Nam":
(gender=F4)
Kết quả là một mảng các giá trị TRUE FALSE như sau:
(TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE)
Trường hợp TRUE tương ứng với "Nam".
Biểu thức thứ hai bên trong SUMPRODUCTS kiểm tra các giá trị trong cột D, Nhóm, so với các giá trị trong G4: G5, "A" và "B". Thử nghiệm này được xử lý với MATCH và ISNA như sau:
ISNA(MATCH(group,G4:G5,0))
Hàm MATCH được sử dụng để so khớp mọi giá trị trong "nhóm" phạm vi được đặt tên với các giá trị trong G4: G5, "A" và "B". Khi trận đấu thành công, MATCH trả về một số. Trường hợp MATCH không thành công, MATCH trả về # N / A. Kết quả là một mảng như thế này:
(1; 2; # N / A; 1; 2; # N / A; 1; 2; # N / A)
Vì giá trị # N / A tương ứng với "không phải A hoặc B", ISNA được sử dụng để "đảo ngược" mảng thành:
(FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE)
Bây giờ TRUE tương ứng với "không phải A hoặc B".
Bên trong SUMPRODUCT, hai kết quả mảng được nhân với nhau, tạo ra một mảng số bên trong SUMPRODUCT:
SUMPRODUCT((0;0;1;0;0;1;0;0;0))
Sau đó hàm SUMPRODUCT trả về tổng, 2, đại diện cho "2 nam không thuộc nhóm A hoặc B".
