Cách Dùng Hàm COUNTIFS Để Thống Kê Nhân Viên Nam Trên 25 Ngày Công Trong 1 Nốt Nhạc

Đếm nhân viên theo nhiều tiêu chí trong bảng chấm công thường tốn 10-15 phút mỗi lần. Tôi từng lọc cột giới tính ra riêng, lọc tiếp số ngày công, rồi đếm thủ công từng hàng. Cứ mỗi lần sếp hỏi “có bao nhiêu nhân viên nam đủ 25 ngày công tháng này”, lại phải filter lại từ đầu. Giờ đây cùng dữ liệu đó, tôi chỉ cần 5 giây với một công thức duy nhất.

Mục Lục

COUNTIFS đếm theo nhiều điều kiện cùng lúc

Khác với hàm COUNTIF chỉ đếm một điều kiện, COUNTIFS cho phép kiểm tra tối đa 127 điều kiện trên các cột khác nhau cùng một lúc. Ô chỉ được đếm khi thỏa mãn tất cả các điều kiện bạn đặt ra.

Cú pháp cơ bản: =COUNTIFS(vùng_điều_kiện_1, điều_kiện_1, vùng_điều_kiện_2, điều_kiện_2, ...)

Mỗi cặp vùng và điều kiện hoạt động như một bộ lọc. Excel kiểm tra từng ô, nếu tất cả điều kiện đều đúng thì đếm lên 1. Các vùng điều kiện phải có cùng số hàng, nhưng không nhất thiết phải kề nhau.

Ví dụ nhanh:

Vùng điều kiện 1: C2:C50 (cột giới tính)
Điều kiện 1: “Nam”
Vùng điều kiện 2: D2:D50 (cột ngày công)
Điều kiện 2: 25

Excel sẽ đếm những hàng nào có giới tính là Nam VÀ có đúng 25 ngày công.

Đếm nhân viên nam có đủ 25 ngày công

Giả sử bảng chấm công có cột B là họ tên, cột C là giới tính, cột D là số ngày công. Dữ liệu chạy từ hàng 2 đến hàng 50.

Công thức cơ bản:

=COUNTIFS(C2:C50,"Nam",D2:D50,25)

Công thức này tìm trong cột C những ô có giá trị “Nam”, đồng thời kiểm tra cột D những ô có giá trị đúng bằng 25. Chỉ những hàng thỏa mãn cả hai điều kiện mới được đếm.

Lưu ý quan trọng: điều kiện text phải để trong dấu ngoặc kép. Nếu viết =COUNTIFS(C2:C50,Nam,D2:D50,25) không có ngoặc kép, Excel sẽ báo lỗi vì tưởng “Nam” là tên ô hoặc biến.

XEM THÊM: Cách Sử Dụng Hàm MAX Trong Excel Để Tìm Giá Trị Lớn Nhất Trong 5 Phút

Điều kiện số không cần ngoặc kép nếu là giá trị cố định. Nhưng nếu dùng toán tử so sánh thì phải có: ">=25" hoặc ">25".

Các biến thể hữu ích:

Đếm nhân viên nam có trên 25 ngày công:

=COUNTIFS(C2:C50,"Nam",D2:D50,">25")

Đếm nhân viên nam có từ 22-26 ngày công:

=COUNTIFS(C2:C50,"Nam",D2:D50,">=22",D2:D50,"<=26")

Lưu ý cú pháp: khi dùng 2 điều kiện cho cùng một cột, phải viết lại cả vùng dữ liệu hai lần. Excel kiểm tra vùng D2:D50 với điều kiện lớn hơn hoặc bằng 22, sau đó kiểm tra lại cùng vùng đó với điều kiện nhỏ hơn hoặc bằng 26.

Thêm điều kiện phòng ban hoặc chức danh

Trong thực tế, bạn thường cần lọc thêm phòng ban. Giả sử cột E chứa phòng ban, bạn muốn đếm nhân viên nam phòng Kinh doanh có đủ 25 ngày công.

Công thức với 3 điều kiện:

=COUNTIFS(C2:C50,"Nam",D2:D50,25,E2:E50,"Kinh doanh")

Công thức đọc được như: “Đếm những hàng có cột C là Nam VÀ cột D bằng 25 VÀ cột E là Kinh doanh”.

Nếu cột F chứa chức danh và bạn chỉ muốn đếm nhân viên chính thức:

=COUNTIFS(C2:C50,"Nam",D2:D50,25,E2:E50,"Kinh doanh",F2:F50,"Chính thức")

Tôi thường dùng công thức này cho báo cáo cuối tháng. Thay vì filter thủ công mất 12-15 phút, giờ chỉ cần 30 giây nhập công thức và kéo xuống các ô bên dưới để đếm cho từng phòng ban.

Đếm theo khoảng ngày công linh hoạt

Thay vì hard-code số 25 trong công thức, bạn có thể tham chiếu đến một ô khác. Giả sử ô G2 chứa ngưỡng ngày công tối thiểu.

Công thức tham chiếu:

=COUNTIFS(C2:C50,"Nam",D2:D50,">="&G2)

Dấu & nối điều kiện “>=” với giá trị trong ô G2. Nếu G2 chứa số 25, công thức sẽ đếm nhân viên nam có 25 ngày công trở lên. Thay đổi số trong G2, kết quả tự động cập nhật.

Tương tự cho khoảng giá trị:

=COUNTIFS(C2:C50,"Nam",D2:D50,">="&G2,D2:D50,"<="&H2)

G2 chứa giá trị tối thiểu, H2 chứa giá trị tối đa. Công thức đếm nhân viên nam có ngày công trong khoảng từ G2 đến H2.

Sử dụng ký tự đại diện cho tên hoặc phòng ban

Khi cần đếm những phòng ban có tên gần giống nhau, dấu sao và dấu chấm hỏi giúp tiết kiệm thời gian.

Dấu sao (*) thay thế cho nhiều ký tự bất kỳ. Dấu chấm hỏi (?) thay thế cho một ký tự duy nhất.

Ví dụ thực tế:

Đếm nhân viên nam ở các phòng có chữ “Kinh doanh” trong tên:

=COUNTIFS(C2:C50,"Nam",D2:D50,25,E2:E50,"*Kinh doanh*")

Công thức này bắt được “Kinh doanh 1”, “Kinh doanh 2”, “Phòng Kinh doanh miền Bắc”, v.v.

XEM THÊM: Cách Sử Dụng Hàm NORM.S.DIST Để Tính Phân Bố Chuẩn Trong 5 Phút

Đếm nhân viên có họ Nguyễn:

=COUNTIFS(C2:C50,"Nam",D2:D50,25,B2:B50,"Nguyễn *")

Dấu cách sau “Nguyễn” quan trọng để tránh bắt nhầm các họ khác có chứa chữ “Nguyễn”.

Lỗi thường gặp và cách sửa trong 2 phút

Lỗi 1: Công thức trả về 0 dù biết chắc có dữ liệu

Nguyên nhân phổ biến nhất: quên ngoặc kép cho điều kiện text. Kiểm tra lại công thức, đảm bảo "Nam" có dấu ngoặc kép, không phải Nam không ngoặc.

Nguyên nhân thứ hai: dữ liệu có khoảng trắng thừa. Cột giới tính có thể chứa “Nam ” với khoảng trắng phía sau thay vì “Nam”. Dùng hàm TRIM để dọn dữ liệu: tạo cột phụ với công thức =TRIM(C2), kéo xuống, rồi copy toàn bộ cột mới này paste đè lên cột cũ dạng Values.

Lỗi 2: Báo lỗi “There’s a problem with this formula”

Excel hiện thông báo này khi toán tử so sánh không đúng cú pháp. Ví dụ viết D2:D50,>=25 sẽ lỗi. Phải viết D2:D50,">=25" với toán tử và số trong cùng một chuỗi ngoặc kép.

Lỗi tương tự xảy ra khi viết D2:D50,>25 không có ngoặc kép. Excel coi >25 là một phép toán chứ không phải điều kiện. Sửa thành ">=25" hoặc ">25" là được.

Lỗi 3: Kết quả sai khi kéo công thức xuống

Các vùng dữ liệu bị thay đổi khi kéo công thức. Cố định vùng bằng dấu đô la: thay C2:C50 thành $C$2:$C$50. Dấu $ trước chữ C cố định cột, dấu $ trước số 2 và 50 cố định hàng.

Công thức hoàn chỉnh cố định:

=COUNTIFS($C$2:$C$50,"Nam",$D$2:$D$50,25)

Kéo xuống bao nhiêu dòng công thức cũng không đổi, trừ khi bạn thay đổi điều kiện.

Lỗi 4: Các vùng điều kiện không cùng số hàng

Excel yêu cầu tất cả vùng điều kiện phải có cùng số hàng. Nếu vùng 1 là C2:C50 (49 hàng) nhưng vùng 2 là D2:D60 (59 hàng), công thức sẽ báo lỗi hoặc cho kết quả không chính xác.

Kiểm tra nhanh: đếm số hàng trong mỗi vùng. C2:C50 có 49 hàng, D2:D50 cũng có 49 hàng. Đảm bảo tất cả vùng đều có 49 hàng.

Kết hợp COUNTIFS với SUMIFS cho báo cáo đầy đủ

Sau khi biết số lượng nhân viên thỏa điều kiện, bạn thường cần tính tổng lương hoặc thưởng của họ. Hàm SUMIFS hoạt động tương tự COUNTIFS nhưng cộng giá trị thay vì đếm số lượng.

Giả sử cột G chứa lương cơ bản, bạn muốn tính tổng lương của nhân viên nam có đủ 25 ngày công:

=SUMIFS(G2:G50,C2:C50,"Nam",D2:D50,25)

Cú pháp khác COUNTIFS một chút: vùng tính tổng (G2:G50) đặt đầu tiên, sau đó mới đến các cặp vùng điều kiện và điều kiện.

XEM THÊM: Hàm AVERAGE Excel: Sau 3 Năm Tính Trung Bình Sai, Tôi Mới Biết Điều Này

Tôi thường tạo bảng tổng hợp với hai cột: cột đầu dùng COUNTIFS đếm số lượng, cột thứ hai dùng SUMIFS tính tổng lương. Sếp có đầy đủ thông tin chỉ trong một bảng duy nhất.

Đếm nhân viên nghỉ dưới 2 ngày

Ngược lại với đếm ngày công, đôi khi cần đếm nhân viên nghỉ ít. Giả sử cột H chứa số ngày nghỉ.

Đếm nhân viên nam nghỉ dưới 2 ngày:

=COUNTIFS(C2:C50,"Nam",H2:H50,"<2")

Đếm nhân viên nam có đủ 25 ngày công VÀ nghỉ dưới 2 ngày:

=COUNTIFS(C2:C50,"Nam",D2:D50,25,H2:H50,"<2")

Công thức này hữu ích cho việc xét thưởng chuyên cần. Thay vì kiểm tra từng người một, bạn có con số chính xác trong 5 giây.

Xử lý trường hợp ô trống

Excel tự động coi ô trống là giá trị 0 trong COUNTIFS. Nếu cột ngày công có ô trống (nhân viên mới chưa chấm công), điều kiện D2:D50,">0" sẽ bỏ qua các ô trống đó.

Đếm chỉ những nhân viên đã có dữ liệu chấm công:

=COUNTIFS(C2:C50,"Nam",D2:D50,">0")

Ngược lại, đếm nhân viên chưa có dữ liệu:

=COUNTIFS(C2:C50,"Nam",D2:D50,"")

Dấu ngoặc kép rỗng "" đại diện cho ô trống.

Khi nào nên dùng COUNTIFS thay vì filter thủ công

Tôi dùng COUNTIFS khi cần số liệu nhanh hoặc phải cập nhật thường xuyên. Filter thủ công thích hợp khi cần xem chi tiết từng dòng dữ liệu.

COUNTIFS tiết kiệm thời gian cho các báo cáo lặp lại. Tạo template một lần, tháng sau chỉ việc paste dữ liệu mới vào, tất cả con số tự động cập nhật.

Với dữ liệu trên 500 dòng, filter thủ công dễ bỏ sót. COUNTIFS không bao giờ bỏ sót miễn là công thức đúng.

Tương thích và lưu ý phiên bản

COUNTIFS hoạt động từ Excel 2007 trở đi. Nếu đồng nghiệp dùng Excel 2003, họ không mở được file có hàm này. Trong trường hợp đó, dùng SUMPRODUCT kết hợp điều kiện hoặc lưu file dạng tương thích.

Google Sheets hỗ trợ COUNTIFS với cú pháp tương tự, chỉ khác dấu phân cách có thể là dấu phẩy hoặc dấu chấm phẩy tùy cài đặt vùng.

Các phiên bản Excel hiện đại (Microsoft 365, Excel 2019, 2021) cho phép tối đa 127 cặp điều kiện, đủ dùng cho mọi trường hợp thực tế. Tôi chưa bao giờ cần quá 8 điều kiện trong một công thức.

Kết quả sau một tháng sử dụng

Báo cáo nhân sự cuối tháng từng tốn 2 giờ để filter và đếm thủ công. Giờ đây cùng báo cáo chỉ mất 15 phút: 5 phút paste dữ liệu, 10 phút kiểm tra và định dạng. Các công thức COUNTIFS và SUMIFS đã có sẵn trong template, chỉ cần dữ liệu mới là tự động cập nhật toàn bộ.

Hàm này hoạt động tốt với Excel 2007 trở lên. Nếu cần đếm theo logic OR thay vì AND, cộng nhiều hàm COUNTIFS lại với nhau: =COUNTIFS(C2:C50,"Nam",D2:D50,25)+COUNTIFS(C2:C50,"Nữ",D2:D50,25) đếm cả nam lẫn nữ có đủ 25 ngày công. Với dữ liệu lớn hơn 10,000 dòng, cân nhắc dùng Power Query cho tốc độ xử lý tốt hơn.