Bạn đang muốn đếm dữ liệu theo nhiều điều kiện trong Excel nhưng chưa biết cách? Bài viết này sẽ hướng dẫn cách sử dụng hàm COUNTIFS từ cơ bản đến nâng cao, giúp bạn xử lý nhanh các tình huống như lọc đơn hàng, thống kê nhân sự hay phân tích điểm số học sinh.
Danh sách
Hàm COUNTIFS là gì? 4 Cách sử dụng hàm COUNTIFS trong Excel
Hàm COUNTIFS là một hàm thống kê nâng cao trong Excel, dùng để đếm số ô thỏa mãn đồng thời nhiều điều kiện khác nhau. Đây là phiên bản mở rộng của hàm COUNTIF (chỉ đếm theo 1 điều kiện), cho phép người dùng xử lý dữ liệu phức tạp với độ chính xác cao.
Ví dụ: bạn muốn đếm có bao nhiêu nhân viên thuộc phòng Kế toán và giới tính nữ thì COUNTIFS sẽ là công cụ phù hợp để thực hiện.
Cú pháp tổng quát:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
Trong đó:
criteria_range: vùng dữ liệu cần xét điều kiện.criteria: điều kiện cần thỏa mãn trong vùng dữ liệu.Một số người dùng nhầm lẫn giữa COUNTIF và COUNTIFS khi cần xử lý nhiều điều kiện:
| Đặc điểm | COUNTIF | COUNTIFS |
|---|---|---|
| Số điều kiện hỗ trợ | 1 | 2 trở lên |
| Độ linh hoạt | Giới hạn – cần thủ thuật hỗ trợ | Tích hợp sẵn khả năng nhiều điều kiện |
| Cách viết | Dễ hơn cho người mới | Tối ưu hơn khi phân tích nâng cao |
Ví dụ khác biệt:
=COUNTIF(B2:B100,"Kế toán")=COUNTIFS(B2:B100,"Kế toán", C2:C100,"Nữ") (nhiều điều kiện)Hàm COUNTIFS được dùng rất rộng rãi trong các lĩnh vực công việc như:
Cách sử dụng hàm COUNTIFS trong Excel chi tiết
Công thức tổng quát:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
Trong đó:
criteria_range1, criteria_range2,… là các vùng dữ liệu chứa giá trị cần so sánh.criteria1, criteria2,… là các điều kiện tương ứng mà bạn muốn áp dụng cho các vùng dữ liệu đó.Bạn có thể sử dụng từ 2 đến 127 cặp điều kiện trong một hàm COUNTIFS.
Ví dụ minh họa:
=COUNTIFS(A2:A100, "Kế toán", B2:B100, "Nữ")
Trong công thức trên:
A2:A100 là vùng chứa tên phòng ban."Kế toán" là điều kiện 1 cần thỏa mãn.B2:B100 là vùng chứa giới tính."Nữ" là điều kiện 2.Ý nghĩa: đếm số dòng mà nhân viên thuộc phòng Kế toán và là nữ.
a. Đảm bảo các vùng dữ liệu có cùng số hàng
Ví dụ:
A2:A100 và B2:B100 hợp lệ.A2:A100 và B2:B90 → sai, kết quả trả về lỗi vì vùng không khớp số dòng.b. Điều kiện nên nằm trong dấu ngoặc kép nếu là văn bản
"Kế toán"Kế toán (Excel hiểu là tên vùng hoặc lỗi công thức)c. Với các phép so sánh logic, đặt toán tử trong chuỗi
Ví dụ:
">=500" – đếm các ô có giá trị lớn hơn hoặc bằng 500."<>" & "Hủy" – đếm các đơn hàng không bị hủy.a. Kết hợp với hàm DATE để lọc theo ngày tháng
Ví dụ: =COUNTIFS(C2:C100, ">=" & DATE(2024,1,1), C2:C100, "<=" & DATE(2024,12,31))
→ Đếm các dòng có ngày trong năm 2024.
b. Dùng hàm TEXT để so sánh ngày dưới dạng văn bản
Nếu dữ liệu ngày được định dạng văn bản như “01/01/2024”:
=COUNTIFS(A2:A100, TEXT(DATE(2024,1,1), "dd/mm/yyyy"))
c. Dùng VALUE để xử lý dữ liệu dạng số bị lưu nhầm thành text
Nếu cột số lưu dạng văn bản, bạn dùng thêm VALUE:
=COUNTIFS(A2:A100, VALUE("100"))
| Ký tự | Ý nghĩa | Ví dụ |
|---|---|---|
* |
Đại diện cho nhiều ký tự bất kỳ | "*Hà Nội*" tìm ô có chuỗi chứa “Hà Nội” |
? |
Đại diện cho một ký tự bất kỳ | "Ng?c" tìm “Ngọc”, “Ngúc”, v.v. |
> |
Lớn hơn | ">1000" |
< |
Nhỏ hơn | "<500" |
<> |
Khác với | "<>Đã giao" |
Tóm lại, khi bạn cần đếm dữ liệu theo nhiều điều kiện kết hợp, hãy sử dụng COUNTIFS đúng cách, kết hợp thêm các hàm hỗ trợ để xử lý dữ liệu phức tạp, từ đó tăng độ chính xác và hiệu suất trong báo cáo Excel.
Ví dụ minh họa sử dụng COUNTIFS trong Excel
Để hiểu rõ hàm COUNTIFS, hãy cùng xem các ví dụ thực tế thường gặp trong báo cáo nhân sự, bán hàng và giáo dục:
1. Đếm số nhân viên nữ trong phòng Kế toán
Dữ liệu mẫu:
| A | B |
|---|---|
| Phòng ban | Giới tính |
| Kế toán | Nữ |
| Kinh doanh | Nam |
| Kế toán | Nữ |
| Kế toán | Nam |
| Nhân sự | Nữ |
Công thức:
=COUNTIFS(A2:A6, "Kế toán", B2:B6, "Nữ")
Giải thích:
– A2:A6 là vùng chứa phòng ban
– B2:B6 là vùng chứa giới tính
→ Hàm trả về 2, là số nhân viên nữ thuộc phòng Kế toán.
2. Thống kê số đơn hàng >10 triệu tại khu vực TP.HCM
Dữ liệu mẫu:
| A | B |
|---|---|
| Khu vực | Giá trị đơn hàng |
| TP.HCM | 8.000.000 |
| Hà Nội | 12.500.000 |
| TP.HCM | 15.000.000 |
| TP.HCM | 20.000.000 |
| Đà Nẵng | 11.000.000 |
Công thức:
=COUNTIFS(A2:A6, "TP.HCM", B2:B6, ">10000000")
Giải thích:
→ Kết quả là 2 đơn hàng ở TP.HCM có giá trị trên 10 triệu.
3. Tính số học sinh có điểm Toán >8 và Văn >8
Dữ liệu mẫu:
| A | B |
|---|---|
| Toán | Văn |
| 9 | 8.5 |
| 7 | 9 |
| 8.5 | 9.5 |
| 9 | 7 |
| 8 | 8 |
Công thức:
=COUNTIFS(A2:A6, ">8", B2:B6, ">8")
Giải thích:
→ Có 2 học sinh đạt điểm cả Toán và Văn đều trên 8.
Những lỗi thường gặp khi sử dụng COUNTIFS
Ví dụ: “10000000” lưu dưới dạng text, nhưng so sánh với >10000000 (number) sẽ không khớp.
Cách xử lý:
– Dùng hàm VALUE() để chuyển văn bản thành số:
=COUNTIFS(B2:B6, ">" & VALUE("10000000"))
– Hoặc định dạng lại cột dữ liệu về dạng Number trong Excel.
Ví dụ: "Kế toán " (dư dấu cách) sẽ không khớp với "Kế toán"
Cách xử lý:
– Dùng hàm TRIM() để loại bỏ khoảng trắng dư:
=COUNTIFS(A2:A6, TRIM("Kế toán"))
Ví dụ:
criteria_range1 là A2:A10criteria_range2 là B2:B8Cách xử lý:
– Luôn đảm bảo các criteria_range có cùng số dòng.
Ví dụ: ngày lưu ở cột A là dạng “01/01/2024” nhưng bạn lọc bằng “2024-01-01” dưới dạng văn bản → sai.
Cách xử lý:
– Dùng hàm TEXT() để chuẩn hóa kiểu ngày:
=COUNTIFS(A2:A100, TEXT(DATE(2024,1,1),"dd/mm/yyyy"))
Hiểu rõ và áp dụng đúng cách viết công thức COUNTIFS sẽ giúp bạn nâng cao độ chính xác khi xử lý dữ liệu Excel. Đặc biệt, kết hợp với các hàm hỗ trợ như TEXT, TRIM, VALUE, bạn sẽ dễ dàng kiểm soát lỗi thường gặp và tăng hiệu suất làm việc.
Để giúp bạn dễ dàng áp dụng hàm COUNTIFS trong công việc thực tế, dưới đây là một số gợi ý về các file mẫu kèm ứng dụng cụ thể:
1. File báo cáo đơn hàng theo khu vực và giá trị
Mục tiêu: Đếm số đơn hàng trong từng khu vực có giá trị từ 10 triệu trở lên.
Dữ liệu mẫu:
| A | B |
|---|---|
| Khu vực | Giá trị đơn hàng |
| TP.HCM | 12.000.000 |
| Hà Nội | 8.500.000 |
| TP.HCM | 15.500.000 |
| Đà Nẵng | 20.000.000 |
Công thức COUNTIFS mẫu:
=COUNTIFS(A2:A5, "TP.HCM", B2:B5, ">=10000000")
Giải thích:
– Đếm số đơn hàng tại TP.HCM có giá trị từ 10 triệu trở lên → kết quả là 2.
2. File quản lý nhân sự theo phòng ban, giới tính, tuổi tác
Mục tiêu: Thống kê số nhân viên nữ, thuộc phòng Kế toán, trên 30 tuổi.
Dữ liệu mẫu:
| A | B | C |
|---|---|---|
| Phòng ban | Giới tính | Tuổi |
| Kế toán | Nữ | 32 |
| Kinh doanh | Nam | 35 |
| Kế toán | Nữ | 28 |
| Kế toán | Nữ | 34 |
Công thức COUNTIFS mẫu:
=COUNTIFS(A2:A5, "Kế toán", B2:B5, "Nữ", C2:C5, ">30")
Kết quả: 2 nhân viên đáp ứng 3 tiêu chí.
3. File thống kê học sinh đạt nhiều tiêu chí học bổng
Mục tiêu: Đếm số học sinh có điểm Toán >8, điểm Văn >8 và hạnh kiểm Tốt.
Dữ liệu mẫu:
| A | B | C |
|---|---|---|
| Toán | Văn | Hạnh kiểm |
| 9 | 8.5 | Tốt |
| 7 | 9 | Khá |
| 8.5 | 9.5 | Tốt |
| 9 | 7 | Tốt |
Công thức COUNTIFS mẫu:
=COUNTIFS(A2:A5, ">8", B2:B5, ">8", C2:C5, "Tốt")
Kết quả: 2 học sinh đạt đủ tiêu chí học bổng.
Mỗi file mẫu đều bao gồm:
Nếu bạn thường xuyên phải làm việc với báo cáo Excel, phân tích số liệu hoặc theo dõi KPI thì thành thạo COUNTIFS sẽ giúp bạn:
Và để phát huy tối đa khả năng làm việc với Excel, môi trường văn phòng hiện đại, yên tĩnh, có hỗ trợ kỹ thuật là điều cần thiết.
King Office – đơn vị cho thuê văn phòng chuyên nghiệp tại TP.HCM, cam kết:
Liên hệ ngay để chọn đúng không gian làm việc lý tưởng:
