Hàm COUNTIF, COUNTIFS trong Excel: đếm có điều kiện
Cách dùng hàm COUNTIF, COUNTIFS đếm theo một hoặc nhiều điều kiện, đếm trùng, đếm ô có dữ liệu với COUNTA, COUNTBLANK, COUNT và ký tự đại diện * ?.
Muốn biết có bao nhiêu nhân viên phòng Kế toán, bao nhiêu học sinh đạt điểm từ 8 trở lên hay mã hàng nào bị nhập trùng, bạn dùng nhóm hàm đếm. COUNTIF đếm theo một điều kiện, COUNTIFS đếm theo nhiều điều kiện cùng lúc; COUNT, COUNTA, COUNTBLANK đếm ô số, ô có dữ liệu và ô trống.
Cú pháp COUNTIF và COUNTIFS
=COUNTIF(range, criteria)
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Đối số | Ý nghĩa |
|---|---|
range / criteria_range | Vùng ô cần xét |
criteria | Điều kiện: "Kế toán", ">=8", "<>" (khác rỗng), hoặc ô chứa điều kiện |
Với COUNTIFS, các vùng điều kiện phải cùng kích thước, nếu không sẽ báo #VALUE!. Máy đặt vùng Việt Nam thay dấu phẩy bằng ;.
Ví dụ thực tế
1. Đếm nhân viên theo phòng ban
=COUNTIF(C2:C200, "Kế toán")COUNTIF không phân biệt chữ hoa, thường: “kế toán” và “Kế Toán” đều được đếm.
2. Đếm học sinh đạt điểm giỏi
=COUNTIF(E2:E45, ">=8")Nếu ngưỡng nằm ở ô H1, nối toán tử với ô: =COUNTIF(E2:E45, ">="&H1). Viết ">=H1" sẽ sai vì Excel hiểu H1 là chữ.
3. COUNTIFS nhiều điều kiện
Đếm học sinh nữ lớp 10A1 có điểm từ 8 trở lên:
=COUNTIFS(B2:B300, "10A1", C2:C300, "Nữ", E2:E300, ">=8")Đếm đơn hàng trong tháng 10/2026 (ngày ở cột A):
=COUNTIFS(A2:A1000, ">="&DATE(2026,10,1), A2:A1000, "<"&DATE(2026,11,1))Muốn cộng tổng theo nhiều điều kiện thay vì đếm, xem SUMIFS, COUNTIFS.
Ký tự đại diện * và ?
| Ký tự | Ý nghĩa | Ví dụ |
|---|---|---|
* | Chuỗi bất kỳ (kể cả rỗng) | "Nguyễn*": tên bắt đầu bằng Nguyễn; "*Hà Nội*": địa chỉ chứa Hà Nội |
? | Đúng một ký tự | "SP??": mã SP kèm đúng 2 ký tự |
~ | Tìm chính ký tự * hoặc ? | "*~?*": ô có chứa dấu hỏi |
Đếm ô có chứa chữ bất kỳ: =COUNTIF(A2:A100, "*"). Ký tự đại diện chỉ áp dụng cho văn bản, không dùng được với số.
Đếm trùng và lọc trùng
- Đánh dấu dòng bị trùng: ở cột phụ gõ
=COUNTIF($A$2:$A$500, A2)>1, kết quả TRUE là mã xuất hiện từ 2 lần. - Chỉ đánh dấu lần xuất hiện thứ 2 trở đi (giữ lại bản đầu):
=COUNTIF($A$2:A2, A2)>1. Chú ý vùng mở rộng dần khi kéo xuống. - Lọc cột phụ theo TRUE để xem hoặc xoá các dòng trùng.
Đếm số giá trị không trùng: trên Microsoft 365 và Google Sheets dùng =COUNTA(UNIQUE(A2:A500)); trên Excel cũ dùng =SUMPRODUCT(1/COUNTIF(A2:A500, A2:A500)) (vùng không được có ô trống). Muốn tô màu dòng trùng, xem định dạng có điều kiện.
COUNT, COUNTA, COUNTBLANK khác nhau thế nào?
| Hàm | Đếm gì | Lưu ý |
|---|---|---|
COUNT | Ô chứa số (kể cả ngày tháng) | Số lưu dạng văn bản không được đếm |
COUNTA | Ô không trống | Đếm cả ô có công thức trả về "" và ô chỉ có dấu cách |
COUNTBLANK | Ô trống | Ô có công thức trả về "" cũng tính là trống |
Ví dụ: đếm số học sinh đã nộp bài (cột F ghi ngày nộp) bằng =COUNTA(F2:F45), chưa nộp bằng =COUNTBLANK(F2:F45).
Đếm ký tự đặc biệt trong một ô
COUNTIF đếm ô, không đếm ký tự bên trong ô. Muốn đếm số dấu phẩy trong A2 (ví dụ để biết có bao nhiêu món trong đơn), dùng LEN và SUBSTITUTE:
=LEN(A2)-LEN(SUBSTITUTE(A2, ",", ""))SUBSTITUTE phân biệt hoa thường. Đếm ký tự trong cả vùng: =SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100, ",", ""))).
Google Sheets
COUNTIF, COUNTIFS, COUNTA, COUNTBLANK và ký tự đại diện dùng giống Excel. Google Sheets có thêm COUNTUNIQUE để đếm giá trị không trùng nhanh.
=SUMPRODUCT(--($A$2:$A$500=A2)) cho an toàn.