Công thức chung
=SUMPRODUCT(ISNUMBER(MATCH(rng1,("A","B"),0))*ISNUMBER(MATCH(rng2,("X","Y","Z"),0)))
Tóm lược
Để đếm các hàng phù hợp với nhiều tiêu chí HOẶC, bạn có thể sử dụng công thức dựa trên hàm SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong F10 là:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,("A","B"),0))* ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0)))
Công thức này trả về tổng số hàng trong đó cột một là A hoặc B và cột hai là X, Y hoặc Z.
Giải trình
Làm việc từ trong ra ngoài, mỗi tiêu chí được áp dụng với một cấu trúc ISNUMBER + MATCH riêng biệt. Để tạo tổng số hàng trong cột một có giá trị là A hoặc B, chúng tôi sử dụng:
ISNUMBER(MATCH(B5:B11,("A","B"),0)
MATCH tạo ra một mảng kết quả giống như sau:
(1;2;#N/A;1;2;1;2)
và ISNUMBER chuyển đổi mảng này thành mảng này:
(TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE)
Để tạo tổng số hàng trong cột hai có giá trị là X, Y hoặc Z, chúng tôi sử dụng:
ISNUMBER(MATCH(C5:C11,("X","Y","Z"),0))
Sau đó MATCH trả về:
(1;2;3;3;#N/A;1;2)
và ISNUMBER chuyển đổi thành:
(TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE)
Hai mảng này được nhân với nhau bên trong SUMPRODUCT, mảng này sẽ tự động chuyển đổi các giá trị TRUE FALSE thành 1 và 0 như một phần của phép toán.
Vì vậy, để hình dung, kết quả cuối cùng được suy ra như sau:
=SUMPRODUCT((1;1;0;1;1;1;1)*(1;1;1;1;0;1;1)) =SUMPRODUCT((1;1;0;1;0;1;1)) =5
Với tham chiếu ô
Ví dụ trên sử dụng hằng số mảng được mã hóa cứng, nhưng bạn cũng có thể sử dụng tham chiếu ô:
=SUMPRODUCT(ISNUMBER(MATCH(B5:B11,E5:E6,0))*ISNUMBER(MATCH(C5:C11,F5:F7,0)))
Thêm tiêu chí
Cách tiếp cận này có thể được "mở rộng quy mô" để xử lý nhiều tiêu chí hơn. Bạn có thể xem một ví dụ trong thử thách công thức này.
