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

Hàm countifs trong excel là một trong những hàm thống kê được sử 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 nhé!

Hàm countif trong excel

Hàm countif thuộc nhóm hàm count sử 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). Có thể là số, tham chiếu hoặc mảng chiếu chứa số. Bỏ qua các giá trị trống.
• Criteria là phần điều kiện (bắt buộc) thực hiện đếm các 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.
• Có thể sử dụng ký tự là dấu chấm hỏi (?) trong điều kiện criteria. Tuy nhiên, tùy vào từng cài đặt trên máy để sử dụng dấu trong hàm là (,) hoặc (;) để sử dụng.

Ví dụ:

Hàm countif trong excel

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

Kết quả áp dụng hàm countif trong excel

► 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 độc lạ là hàm này chứa nhiều điều kiện kèm theo để xét. Trong trường hợp có nhiều điều kiện kèm theo khi xét những giá trị thì nên vận 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ề yêu cầu khi sử dụng dựa vào điều kiện cụ thể.

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 có thể 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 những ô trong một khoanh vùng phạm vi đơn lẻ với một điều kiện kèm theo duy nhất hay trong nhiều vùng tài liệu với nhiều điều kiện kèm theo bạn hoàn toàn có thể vận dụng hàm countifs. Vùng chọn bổ trợ cùng số hàng và cột như vùng tài liệu tiên phong ( Đối số criteria_range1i ).

Có thể đếm vùng dữ liệu liền kề và không liền kề.

Hàm countifs giải quyết và xử lý như một giá trị bằng không ( 0 ) trong tiêu chuẩn là một tham chiếu đến một ô trống.

Bạn có thể á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ê thu nhập, để chính sách tự động hóa thống kê số lượng nhân viên cấp dưới có thu nhập từ 10 triệu trở lên vận dụng cấu trúc như sau :

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

Trong đó:

• C2:C10 là cột chức vụ 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 thu nhập 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ó thu nhập hàng tháng >=10 triệu đồng.

Tại ví dụ này, hàm countifs trong excel sẽ thỏa mãn nhu cầu hai điều kiện kèm theo bên trên, tự động hóa bỏ lỡ những người không có chức vụ nhân viên cấp dưới và thu nhập dưới 10 triệu. Hàm countifs trong excel

Cách sử 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 tài liệu mẫu sản phẩm như trong bảng phía dưới. Bạn muốn đếm số hàng tồn dư ( giá trị cột B > 0 ) nhưng chưa bán được ( 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 ” ) : Ví dụ cách đếm ô tính nhiều tiêu chí khác nhau

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 những mục có tiêu chuẩn giống nhau bạn phải phân phối mỗi cặp criteria_range / criteria riêng không liên quan gì đến nhau. Trong bảng tài liệu phía dưới để đếm những mục chứa 0 trong cột B và cột C cần vận 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ó giá trị “ 0 ” trong cả hai cột ) hàm countifs với các tiêu chí giống nhau

Á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 những ô tính phân phối toàn bộ mọi tiêu chuẩn chỉ định rất thuận tiện khi vận dụng hàm countifs. Nhưng nếu bạn muốn đếm những ô có tối thiểu một trong những tiêu chuẩn chỉ định là “ TRUE ” cần sử dụng thêm 1 số ít công chức của hàm countif hoặc vận 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 tài liệu bên dưới, nếu bạn muốn tính những đơn hàng đang ở trạng thái “ Cancelled ” và “ Pending ” bạn vận dụng công thức hàm countif thông thường và tác dụng hiển thị.

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

Cách đếm số ô tính nhiều tiêu chí Nếu mỗi hàm chứa nhiều hơn 1 điều kiện kèm theo thì vận dụng hàm countifs thay vì sử dụng hàm countif. Cụ thể, khi đếm số đơn đặt hàng “ Cancelled ” và “ Pending ” so với “ Apples ”, sử 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”)

Kết quả cách đếm số ô tính nhiều tiêu chí

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

Trong công thức excel, 1 số ít những ký tự đại diện thay mặt cơ bản bạn hoàn toàn có thể sử dụng sau đây :

• Dấu hỏi (?): Kết hợp được với bất kỳ ký tự đơn nào, sử 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, sử dụng để đếm ô chứa một từ cụ thể 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 list những dự án Bất Động Sản trong cột A. Bạn muốn biết có bao nhiêu dự án Bất Động Sản đã ký kết ( chứa tên người trong cột B ). Hãy thêm một điều kiện kèm theo thứ 2 ( End date trong cột D ).

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

Lưu ý:

Không thể vận dụng ký tự đại diện thay mặt trong tiêu chuẩn thứ 2 vì cột D chứa kiểu tài liệu ngày tháng, không phải định dạng text. Đây là nguyên do tại bạn bạn nên sử dụng tiêu chuẩn “ < > ” và ” ” để dò tìm ô chứa tài liệu. Hàm countifs với các ký tự đại diện Hi vọng những san sẻ có ích trên đây sẽ giúp những bạn nắm rõ hơn về hai hàm countif, countifs trong excel và cách sử dụng chúng. Đừng quên update thêm nhiều kỹ năng và kiến thức tin học văn phòng cơ bản tại chuyên trang thông tin việc làm News. timviec.com.vn nhé. Chúc những bạn thành công xuất sắc !

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

Source: http://wp.ftn61.com
Category: Hỏi Đáp