[TIP] Cách dùng hàm đếm countif và countifs có điều kiện trong excel

Hàm countifs trong excel là một trong số những hàm thống kê được dùng nhiều khi làm việc trong excel. Trong đó, hàm count dùng để đếm các ô chứa số còn hàm countifs dùng để đếm các ô theo một điều kiện nhất định (ngày tháng, số, text hoặc ô chứa dữ liệu). Để nắm rõ hơn các bạn hãy đọc hướng dẫn dưới đây để hiểu rõ hơn về hàm countif và hàm countifs nha!

Hàm countif trong excel

Hàm countif thuộc nhóm hàm count dùng để đếm, trong đó hàm countif là hàm đếm có điều kiện và là sự kết hợp của hàm count và hàm if trong excel.

Hàm countif trên Excel có cú pháp là =COUNTIF(range;criteria)

Trong đó:

• Range là phần vùng đếm dữ liệu (bắt buộc). khả năng là số, tham chiếu hoặc mảng chiếu chứa số. Bỏ qua các tổng giá trị trống.
• Criteria là phần điều kiện (bắt buộc) thực hiện đếm các tổng giá trị bên trong range, số, biểu thức, tham chiếu ô hoặc văn bản.

Lưu ý:

• Hàm countif trả về kết quả có điều kiện với các chuỗi ký tự hơn 255 ký tự.
• Đối số criteria để trong ngoặc kép không phân biệt chữ thường hay in hoa.
• khả năng dùng ký tự là dấu chấm hỏi (?) trong điều kiện criteria. mặc khác, tùy vào từng cài đặt trên máy để dùng dấu trong hàm là (,) hoặc (;) để dùng.

Ví dụ:

Để đếm được số học sinh nam cần áp dụng công thức cho ô D15 như sau: =COUNTIF(C5:C14,”Nam”)

► XEM THÊM: Các hàm cơ bản trong Excel mà “dân” văn phòng phải biết

Hàm countifs

Khái niệm hàm countifs trong excel

Hàm countifs trong excel là một hàm nâng cao của hàm đếm count. Nhưng có một điểm khác biệt là hàm này chứa nhiều điều kiện để xét. Trong trường hợp có nhiều điều kiện khi xét các tổng giá trị thì nên áp dụng hàm countifs.

Trên lý thuyết, hàm count, hàm countif, hàm countifs đều là hàm đếm trong excel, chủ yếu khác nhau về bắt buộc khi dùng dựa vào điều kiện chi tiết.

Lệnh countifs trong excel

Cú pháp lệnh hàm countifs: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…).

Trong đó:

• Criteria_range1: Vùng chọn đầu tiên cần thống kê (bắt buộc).
• Criteria1: Đặt điều kiện áp dụng cho định dạng khả năng là số, ô tham chiếu, chuỗi văn bản, một mảng hoặc một hàm Excel khác, (bắt buộc).
• [criteria_range2, criteria2]: Cặp vùng chọn và điều kiện bổ sung tối đa cho phép 127 cặp vùng chọn và điều kiện.

Lưu ý:

Để đếm các ô trong một phạm vi đơn lẻ với một điều kiện duy nhất hay trong nhiều vùng dữ liệu với nhiều điều kiện bạn khả năng áp dụng hàm countifs.

Vùng chọn bổ sung cùng số hàng và cột như vùng dữ liệu đầu tiên (Đối số criteria_range1i).

khả năng đếm vùng dữ liệu liền kề và không liền kề.

Hàm countifs xử lý như một tổng giá trị bằng không (0) trong tiêu chí là một tham chiếu đến một ô trống.

Bạn khả năng áp dụng các wildcard characters trong các tiêu chí như:

• Dấu hoa thị (*)
• Dấu hỏi chấm (?)

Cách dùng hàm countifs

Với bảng thống kê mức lương, để chế độ tự động thống kê số lượng nhân viên có mức lương từ 10 triệu trở lên áp dụng cấu trúc như sau:

=COUNTIFS(C2:C10,”Nhân viên”,D2:D10,”>=10000000″)

Trong đó:

• C2:C10 là cột cấp bậc trong bảng, điều kiện chính là “nhân viên”. Máy sẽ đọc dữ liệu điều kiện đầu tiên là đếm số lượng nhân viên trong công ty.
• D2:D10 là cột mức lương 1 tháng, điều kiện “>=10000000”. Máy sẽ đọc dữ liệu điều kiện thứ 2 chính là số lượng người có mức lương mỗi tháng >=10 triệu đồng.

ở ví dụ này, hàm countifs trong excel sẽ thỏa mãn hai điều kiện bên trên, tự động bỏ qua những người không có cấp bậc nhân viên và mức lương dưới 10 triệu.

► XEM THÊM: Cách dùng các hàm tìm kiếm trong Excel [HƯỚNG DẪN]

Cách dùng hàm countif và hàm countifs nhiều điều kiện

Cách đếm ô tính với nhiều tiêu chí (and logic)

Công thức 1: Công thức countifs với các tiêu chí khác

Nếu bạn có một bảng dữ liệu danh mục như trong bảng phía dưới. Bạn muốn đếm số hàng tồn kho (tổng giá trị cột B>0) nhưng chưa bán được (tổng giá trị cột C=0)

Công thức áp dụng: = COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”)

Kết quả hiển thị là 2 (“Cherries” và “Lemons”):

Công thức 2. Công thức countifs với các tiêu chí giống nhau

Khi bạn muốn đếm các mục có tiêu chí giống nhau bạn phải cung cấp mỗi cặp criteria_range / criteria riêng lẻ. Trong bảng dữ liệu phía dưới để đếm các mục chứa 0 trong cột B và cột C cần áp dụng công thức như sau:

= COUNTIFS ($B$2: $B$7, “= 0”, $C$2: $C$7, “= 0”)

Kết quả hiển thị trả về là 1 (vì chỉ có “Grapes” có tổng giá trị “0” trong cả hai cột)

Áp dụng công thức đơn giản với một criteria_range duy nhất như COUNTIFS(B2: C7, “= 0”) sẽ trả về kết quả khác, tổng số các ô tính trong khoảng từ B2:C7 đều chưa 0.

Cách đến số ô tính với nhiều tiêu chí (or logic)

Ví dụ dưới đây, việc đếm các ô tính đáp ứng tất cả mọi tiêu chí chỉ định rất đơn giản khi áp dụng hàm countifs. Nhưng nếu bạn muốn đếm các ô có ít nhất một trong các tiêu chí chỉ định là “TRUE” cần dùng thêm một vài công chức của hàm countif hoặc áp dụng công thức hàm sum countifs với hằng số mảng.

Công thức thêm hai hoặc nhiều công thức countif hoặc countifs

Trong bảng dữ liệu bên dưới, nếu bạn muốn tính các đơn hàng đang ở trạng thái “Cancelled” và “Pending” bạn áp dụng công thức hàm countif bình thường và kết quả hiển thị.

Công thức lệnh: =COUNTIF($C$2:$C$11,”Cancelled”)+COUNTIF($C$2:$C$11,”Pending”)

Nếu mỗi hàm chứa nhiều hơn 1 điều kiện thì áp dụng hàm countifs thay vì dùng hàm countif. chi tiết, khi đếm số đơn đặt hàng “Cancelled” và “Pending” đối với “Apples”, dùng công thức sau:

Công thức lệnh như sau:

=COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,”Cancelled”)+COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,”Pending”)

Cách dùng countifs với các ký tự đại diện

Trong công thức excel, một vài những ký tự đại diện cơ bản bạn khả năng dùng sau đây:

• Dấu hỏi (?): Kết hợp được với bất kỳ ký tự đơn nào, dùng đếm các ô bắt đầu hoặc kết thúc bằng một ký tự nhất định.
• Dấu hoa thị (*): Kết hợp được với bất kỳ dãy ký tự nào, dùng để đếm ô chứa một từ chi tiết hoặc một ký tự trong ô.

Nếu muốn đếm ô chứa dấu hỏi hoặc dấu hoa thị, gõ dấu ngã (~) trước dấu chấm hỏi hoặc hoa thị. Ví dụ bạn có một danh sách các dự án trong cột A. Bạn muốn biết có bao nhiêu dự án đã ký kết (chứa tên người trong cột B). Hãy thêm một điều kiện thứ 2 (End date trong cột D).

Công thức lệnh: =COUNTIFS(B2:B10,”*”,D2:D10,””&””))

Lưu ý:

Không thể áp dụng ký tự đại diện trong tiêu chí thứ 2 vì cột D chứa kiểu dữ liệu ngày tháng, không phải định dạng text. Đây là lý do ở bạn bạn nên dùng tiêu chí “”&”” để dò tìm ô chứa dữ liệu.

Hi vọng những chia sẻ hữu ích trên đây sẽ giúp các bạn nắm rõ hơn về hai hàm countif, countifs trong excel và cách dùng chúng. Đừng quên cập nhật thêm nhiều kỹ năng tin học văn phòng cơ bản ở chuyên trang thông tin việc làm News.timviec.com.vn nha. Chúc các bạn thành công!

► Tham khảo thêm: Cách dùng hàm if nhiều điều kiện trong excel