Công thức chung
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Tóm lược
Để đếm các giá trị duy nhất với một hoặc nhiều điều kiện, bạn có thể sử dụng công thức dựa trên UNIQUE và FILTER. Trong ví dụ được hiển thị, công thức trong H7 là:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
trả về 3, vì có ba tên duy nhất trong B6: B15 được liên kết với dự án Omega.
Lưu ý: công thức này yêu cầu Công thức Mảng Động, chỉ khả dụng trong Excel 365. Với phiên bản Excel cũ hơn, bạn có thể sử dụng các công thức thay thế phức tạp hơn.
Giải trình
Về cơ bản, công thức này sử dụng hàm UNIQUE để trích xuất các giá trị duy nhất và hàm FILTER áp dụng các tiêu chí.
Làm việc từ trong ra ngoài, hàm FILTER được sử dụng để áp dụng các tiêu chí và chỉ trích xuất các tên được liên kết với dự án "Omega":
FILTER(B6:B15,C6:C15=H6) // Omega names only
Kết quả từ FILTER là một mảng như thế này:
("Jim";"Jim";"Carl";"Sue";"Carl")
Tiếp theo, hàm UNIQUE được sử dụng để loại bỏ các bản sao:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
dẫn đến một mảng mới như thế này:
("Jim";"Carl";"Sue") // after UNIQUE
Tại thời điểm này, chúng tôi có một danh sách duy nhất các tên liên quan đến Omega và chúng tôi chỉ cần đếm chúng. Vì lý do được giải thích bên dưới, chúng tôi thực hiện việc này với hàm LEN và hàm SUM. Để làm cho mọi thứ rõ ràng, trước tiên chúng tôi sẽ viết lại công thức để bao gồm danh sách duy nhất:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Hàm LEN lấy độ dài của mỗi mục trong danh sách và trả về một mảng độ dài:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Tiếp theo, chúng tôi kiểm tra xem độ dài có lớn hơn 0 hay không:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
Và sử dụng một số âm kép để buộc các giá trị TRUE và FALSE thành 1s và 0s:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Cuối cùng, chúng tôi cộng các kết quả với hàm SUM:
=SUM((1;1;1)) // returns 3
Mảng này được phân phối trực tiếp đến hàm COUNTA, hàm này trả về tổng số cuối cùng:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Lưu ý rằng vì chúng tôi đang kiểm tra độ dài của mỗi mục được trả về bởi UNIQUE, ô trống hoặc ô trống đáp ứng tiêu chí sẽ bị bỏ qua. Công thức này là động và sẽ tính toán lại ngay lập tức nếu dữ liệu nguồn bị thay đổi.
Đếm duy nhất với nhiều tiêu chí
Để đếm các giá trị duy nhất dựa trên nhiều tiêu chí, có thể mở rộng logic "bao gồm" bên trong FILTER. Ví dụ: để đếm các tên riêng cho dự án Omega chỉ trong tháng 6, hãy sử dụng:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Đây là một ví dụ về việc sử dụng logic boolean để áp dụng nhiều hơn một điều kiện. Cách tiếp cận được giải thích chi tiết hơn ở đây.
Để biết thêm chi tiết, hãy xem video đào tạo này: Cách lọc với nhiều tiêu chí.
COUNTA
Có thể viết một công thức đơn giản hơn trả lời trên hàm COUNTA. Tuy nhiên, một lưu ý quan trọng là COUNTA sẽ trả về 1 khi không có giá trị phù hợp. Điều này là do hàm FILTER trả về lỗi khi không có dữ liệu nào phù hợp với tiêu chí và lỗi này sẽ được tính bởi hàm COUNTA. Công thức COUNTA cơ bản trông giống như sau:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Một lần nữa, công thức này sẽ trả về 1 khi không có dữ liệu phù hợp. Nó cũng sẽ bao gồm các ô trống đáp ứng tiêu chí. Công thức dựa trên LEN và SUM là một lựa chọn tốt hơn.
Không có mảng động
Nếu bạn đang sử dụng phiên bản Excel cũ hơn mà không hỗ trợ mảng động, bạn có thể sử dụng một công thức phức tạp hơn. Để thảo luận chung hơn về các lựa chọn thay thế mảng động, hãy xem: Các lựa chọn thay thế cho Công thức Mảng Động.
