Trong Excel, hàm Countif còn có phiên bản số nhiều là hàm Countifs để đếm với 2 điều kiện chọn lọc trở lên được thỏa mãn đồng thời. Bài viết sẽ giới thiệu về hàm Countif và Countifs trong Excel và có bài tập mẫu.
Nếu như hàm SUMIF trong Microsoft Excel dùng để cộng có điều kiện chọn lọc, thì hàm COUNTIF là hàm đếm có điều kiện với tiêu chí chọn lọc thay vì việc đếm cả dãy hàng, dãy cột. Và cũng giống như hàm SUMIF, hàm COUNTIF cũng có một “phiên bản số nhiều” chính là COUNTIFS để đếm với từ 2 tiêu chí trở lên. Rõ ràng nếu bạn biết dùng thông thạo COUNTIF và COUNTIFS thì bạn sẽ áp dụng được trong rất nhiều trường hợp tính toán trong Excel.
Hướng dẫn cách sử dụng hàm COUNTIF trong Excel
Khi bạn dùng hàm COUNTIF bạn có công thức sau:
=COUNTIF(khoảng đếm, tiêu chí đếm)
Trong đó, bất kỳ ô nào trong bảng tính nằm trong khoảng đếm đạt tiêu chí đếm sẽ làm giá trị đếm được cộng 1.
Đối với các tiêu chí đếm, ngoại trừ tiêu chí dùng giá trị chữ số, bất kỳ tiêu chí nào dùng ký tự text hoặc tiêu chí dùng biểu thức toán học đều cần đặt trong ngoặc kép.
Và với tiêu chí đếm, bạn hoàn toàn có thể dùng dấu ? đại diện cho một ký tự bất kỳ và dùng dấu * đại diện cho một chuỗi ký tự bất kỳ trong bảng tính (Nếu thực sự dùng 2 dấu này làm tiêu chí thì chúng ta viết ~? và ~*).
Ví dụ trong một bảng điểm tổng kết như bạn từng xử lý cho hàm IF ở đây, nếu bạn muốn tự động thống kê số lượng sinh viên không đạt điểm 5 trong khóa quân sự thì bạn dùng cấu trúc:
=COUNTIF(B2:B6,”
Sử dụng công thức =COUNTIF(B2:B6,”<5″)
Hướng dẫn cách sử dụng hàm COUNTIF trong Microsoft Excel: Ví dụ trong một bảng điểm tổng kết như trên hình ảnh, nếu bạn muốn tự động thống kê số lượng sinh viên không đạt điểm 5 khóa quân sự thì bạn dùng cấu trúc: =COUNTIF(B2:B6,”.
Kết quả của công thức hàm Countif
Hướng dẫn cách sử dụng hàm COUNTIF trong Microsoft Excel: Mỗi khi giá trị trong khoảng đếm thay đổi thì ô ghi giá trị đếm cũng sẽ được cập nhật theo sự thay đổi đó.
Hướng dẫn cách sử dụng hàm COUNTIFS trong Microsoft Excel
Hàm COUNTIFS dùng để đếm với từ 2 tiêu chí trở lên được thỏa mãn điều kiện, và hàm này có công thức như sau:
=COUNTIFS(khoảng đếm 1, tiêu chí đếm 1, khoảng đếm 2, tiêu chí đếm 2…)
Trong đó bạn hoàn toàn có thể dùng thêm tới 127 cặp tiêu chí đếm và khoảng đếm.
Như với bảng thống kê thu nhập bạn từng xử lý ở đây, để bạn tự động thống kê số lượng nhân viên có thu nhập từ 10 triệu đồng trở lên thì bạn dùng công thức sau:
=COUNTIFS(C2:C10,”Nhân viên”,D2:D10,”>=10000000″)
Sử dụng công thức =COUNTIFS(C2:C10,”Nhân viên”,D2:D10,”>=10000000″)
Hướng dẫn cách sử dụng hàm COUNTIFS trong Microsoft Excel: Với bảng thống kê thu nhập như trên hình, để bạn tự động thực hiện thống kê số lượng nhân viên có thu nhập từ 10 triệu đồng trở lên thì chúng ta dùng cấu trúc sau: =COUNTIFS(C2:C10,”Nhân viên”,D2:D10,”>=10000000″).
Kết quả sau khi sử dụng hàm
Hướng dẫn cách sử dụng hàm Countifs và Countif với nhiều tiêu chí:
Hướng dẫ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 nhau
Giả sử bạn có một bảng tính với danh sách sản phẩm như trong hình bên dưới. Bạn đang muốn biết số lượng hàng tồn kho (giá trị cột B lớn hơn 0) nhưng chưa bán được (giá trị cột C bằng 0).
Nhiệm vụ này bạn hoàn toàn có thể được thực hiện bằng cách sử dụng công thức này:
= COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”)
Kết quả bạn nhận được là 2 (“Cherries” và “Lemons”):
Kết quả nhận được
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 với các tiêu chí giống nhau, bạn vẫn phải cung cấp mỗi cặp criteria_range / criteria một cách riêng lẻ từng cặp.
Ví dụ: Dưới đây là công thức đúng để đếm các mục chứa 0 cả trong cột B và cột C:
= COUNTIFS ($B$2: $B$7, “= 0”, $C$2: $C$7, “= 0”)
Công thức hàm COUNTIFS này trả về 1 vì chỉ có “Grapes” có giá trị “0” trong cả hai cột trong bảng tính trên.
Kết quả trả về
Bạn chỉ cần sử dụng một công thức đơn giản hơn với một criteria_range duy nhất như COUNTIFS(B2: C7, “= 0”) sẽ trả lại bạn kết quả khác – tổng số các ô tính trong khoảng B2:C7 chứa 0 (trong ví dụ này là 4).
Hướng dẫn cách đếm số ô tính với nhiều tiêu chí (OR LOGIC):
Như bạn đã thấy trong ví dụ trên, việc thực hiện đếm các ô tính đáp ứng tất cả các tiêu chí cần chỉ định rất dễ dàng bởi vì hàm COUNTIFS được thiết kế để thực hiện theo cách này.
Nhưng nếu bạn muốn đếm các ô có ít nhất một trong các tiêu chí được chỉ định là TRUE, nghĩa là dựa trên logic OR thì sao? Nhìn chung, bạn sẽ có hai cách để thực điều này – bằng cách thêm một số công thức COUNTIF hoặc sử dụng công thức hàm SUM COUNTIFS với hằng số mảng.
Công thức 1. Thêm hai hoặc nhiều công thức COUNTIF hoặc COUNTIFS
Trong bảng tính bên dưới, giả sử bạn muốn tính các đơn đặt hàng với hai trạng thái “Cancelled” và “Pending“, bạn bạn hoàn toàn có thể viết 2 công thức COUNTIF bình thường và nhận kết quả:
=COUNTIF($C$2:$C$11,”Cancelled”)+COUNTIF($C$2:$C$11,”Pending”)
Kết quả nhận được sau khi áp dụng hàm Countif
Với trường hợp mỗi hàm chứa nhiều hơn một điều kiện, bạn sử dụng hàm COUNTIFS thay vì hàm COUNTIF. Ví dụ: Yêu cầu đếm số đơn đặt hàng “Cancelled” và “Pending” đối với “Apples”, bạn sử dụng công thức 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ả trả về sau khi kết hợp hàm
Công thức 2. Công thức SUM COUNTIFS với hằng mảng
Trong tình huống bạn cần thiết lập nhiều tiêu chí, cách tiếp cận trên không phải là cách tối ưu nhất bởi công thức của bạn đang quá dài dòng. Để thực hiện được các phép tính tương tự trong một công thức nhỏ gọn hơn, bạn hãy liệt kê tất cả các tiêu chí của bạn trong mảng và cung cấp mảng đó cho criteria của hàm COUNTIFS. Để đếm tổng, bạn kết hợp hàm COUNTIFS bên trong hàm SUM theo công thức như sau:
=SUM (COUNTIFS (range, {“criteria1”, “criteria2”, “criteria3”, …}))
Trong bảng mẫu dưới đây, để đếm các đơn đặt hàng có trạng thái “Cancelled“, “Pending” hoặc “In transit“, công thức áp dụng sẽ như sau:
=SUM(COUNTIFS($C$2:$C$11,{“Cancelled”,”Pending”,”In Transit”}))
Áp dụng công thức kết hợp hàm
Tương tự, bạn hoàn toàn có thể đếm các ô dựa trên hai hoặc nhiều hơn các cặp criteria_range/criteria. Ví dụ: Yêu cầu cần biết số lượng đơn đặt hàng “Apples” trong trạng thái “Cancelled”, “Pending”, “In transit”, bạn sử dụng công thức sau đây:
=SUM(COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,{“Cancelled”,”Pending”,”In transit”}))
Kết quả của công thức kết hợp hàm
