Hàm COUNTIF trong Excel và hàm COUNTIFS được sử dụng để đếm dữ liệu và đối tượng ở một chuỗi trong trang tính. Nhưng trong tất cả các hàm Excel, các hàm đếm có điều kiện COUNTIFS và hàm COUNTIF trong Excel có lẽ là khó hiểu nhất vì chúng trông rất giống nhau và cả hai đều phục vụ cùng một mục đích là đếm ô dựa trên các điều kiện nhất định. Bài viết dưới đây, kế toán Bảo Tín sẽ hướng dẫn các bạn cách sử dụng các hàm đếm này, chúng ta cùng tham khảo nhé!
1. Cách sử dụng hàm COUNTIF trong Excel
Như đã đề cập trước đó, hàm COUNTIF trong Excel được sử dụng để đếm ô trong nhiều phạm vi dựa trên một hoặc nhiều điều kiện. Hàm COUNTIFS có sẵn trong Excel 2016, 2013, Excel 2010 và Excel 2007 nên bạn có thể sử dụng các ví dụ sau trong mọi phiên bản Excel.
Cú pháp:
= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)
Trong đó:
criteria_range1: Vùng chứa có điều kiện đầu tiên, đây là vùng bắt buộc phải có.
criteria1: Điều kiện đầu tiên (thuộc phạm vi điều kiện đầu tiên) bao gồm kiểu số, tham chiếu ô, chuỗi văn bản, hàm Excel hoặc các phạm vi Excel khác.
criteria_range2: Vùng chứa điều kiện thứ hai ( yêu cầu dải ô criteria_range2 có độ lớn bằng nhau)
criteria2, …Điều kiện thứ hai (thuộc phạm vi của điều kiện thứ nhất). Bạn có thể chỉ định tối đa 127 cặp phạm vi/ điều kiện trong công thức của mình.
Điều tương tự cũng áp dụng cho các điều kiện khác.
Trên thực tế, bạn không cần phải ghi nhớ cú pháp của hàm đếm có điều kiện COUNTIF: Microsoft Excel hiển thị các đối số của hàm khi bạn bắt đầu nhập; đối số bạn đang nhập sẽ được tô sáng.
Bạn chỉ cần nhớ rằng bạn muốn sử dụng hàm COUNTIFS trong Excel.
Xem thêm: Cách dùng hàm SUMIF trong EXCEL đơn giản và dễ hiểu nhất
Lưu ý:
Bạn có thể sử dụng hàm COUNTIFS trong Excel để đếm ô trong phạm vi điều kiện đơn cũng như trong phạm vi đa điều kiện. Trong trường hợp sau, chỉ các ô đáp ứng tất cả các điều kiện mới được chỉ định mới được tính.
Criteria1 bạn có thể trình bày chúng dưới dạng ví dụ:10, “<=32”, A4, “gord”…
Mỗi khu vực bổ sung phải có cùng số hàng và số cột như phạm vi đầu tiên. Cả hai phạm vi tiếp giáp và không tiếp giáp đều được phép.
Nếu điều kiện là một tham chiếu đến một ô trống, thì hàm COUNTIFS sẽ coi nó như một giá trị 0 (0).
Bạn có thể sử dụng các ký tự đại diện dưới dạng dấu sao (*) và dấu chấm hỏi (?)
2. Cách sử dụng hàm đếm có điều kiện COUNTIFS với nhiều điều kiện
Dưới đây là một số công thức mẫu trình bày cách sử dụng các hàm COUNTIFS trong Excel để đánh giá nhiều điều kiện.
Các ví dụ dưới đây cho bạn thấy cách sử dụng các hàm này trong các trường hợp cụ thể tương ứng cho phép bạn sử dụng hàm COUNTIFS trong Excel để đánh giá nhiều điều kiện khác nhau.
2.1 Cách đếm các ô có nhiều điều kiện
Công thức 1. Công thức COUNTIFS với nhiều điều kiện khác nhau
Ví dụ: nếu bạn có danh sách sản phẩm như hình minh họa bên dưới. Bạn muốn có một số mặt hàng trong kho (giá trị trong cột B lớn hơn 0) nhưng chưa được bán (giá trị trong cột C bằng 0)
Nhiệm vụ có thể được thực hiện với công thức này:
=COUNTIFS(B2:B7,”>0″, C2:C7,”=0″)
Và số lượng là 2 (“Anh đào” và “Chanh”):
Xem thêm: Hướng dẫn dùng hàm UPPER trong Excel để chuyển chữ thường thành chữ in hoa
Công thức 2. Công thức COUNTIFS có cùng các tiêu chí
Nếu bạn muốn đếm các mục có điều kiện giống hệt nhau, bạn vẫn phải bao gồm từng cặp điều kiện-range/điều kiện.
Ví dụ: đây là công thức chính xác để đếm các mục bằng 0 trong cả cột B và cột C:
= COUNTIFS($B$2:$B$7,”=0″, $C$2:$C$7,”=0″).
Công thức COUNTIFS này sẽ trả về 1 vì chỉ “nho” có giá trị 0 trong cả hai cột
2.2 Cách đếm các ô có nhiều điều kiện khác nhau
Như bạn đã thấy trong các ví dụ trên, việc đếm các ô đáp ứng tất cả các điều kiện đã cho là rất dễ dàng vì đó là cách hàm đếm có điều kiện COUNTIFS đã được thiết kế.
Nhưng điều gì sẽ xảy ra nếu bạn muốn đếm các ô có ít nhất một trong các điều kiện được chỉ định là TRUE, tức là dựa trên logic OR? Nói chung, có hai cách để thực hiện việc này, thêm công thức COUNTIFS hoặc sử dụng công thức SUM COUNTIFS.
Công thức 1: Thêm hai hoặc nhiều công thức trong COUNTIFS
Ví dụ: để nhận số lượng đơn đặt hàng “đã hủy” và “đang chờ xử lý” cho “táo”, hãy sử dụng công thức sau:
= COUNTIFS($A$2:$A$11, “Táo”, $C$2:$C$11,”Đã hủy”) + COUNTIFS($A$2:$A$11, “Táo”, $C$2:$C$11,”Đang chờ xử lý”)
Công thức 2: SUMIFS với hằng số là mảng
Trong trường hợp bạn cần đánh giá nhiều điều kiện, phương pháp trên không phải là tốt nhất vì công thức của bạn sẽ quá dài.
Để 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, hãy liệt kê tất cả các điều kiện của bạn trong một hằng số mảng và cung cấp cho mảng này đối số có điều kiện của hàm đếm có điều kiện COUNTIFS.
Ta sẽ dùng như sau:
= SUM(COUNTIFS(range,{“criteria1″,”criteria2″,”criteria3”,…}))
Trong bảng dưới đây, công thức tính “táo” có trạng thái bị hủy, “đang chờ xử lý “hoặc “nhập kho” sẽ là:
= SUM(COUNTIFS($A$2:$A$11,”Táo”,$C$2:$C$11,{“Đã hủy”,”Đang chờ xử lý”,”Nhập kho”}))
Tham khảo: Dịch vụ kế toán trọn gói
2.3 Cách đếm số trong các khoảng cụ thể
Công thức 1: Hàm COUNTIFS đếm các ô trong các khoảng số
Công thức COUNTIFS để đếm các ô giữa 2 số
Để tìm xem có bao nhiêu số từ 5 đến 10 (không kể 5 và 10) trong các ô từ C2 đến C10, hãy sử dụng công thức sau:
= COUNTIFS (C2: C10, “> 5”, C2: C10, “<10”)
Để đếm các số 5 và 10 khi đếm, hãy sử dụng công thức “lớn hơn hoặc bằng” và “nhỏ hơn hoặc bằng”:
= COUNTIFS (B2: B10, “> = 5”, B2: B10, “<= 10”).
Công thức 2: Công thức hàm COUNTIF trong Excel để đếm giữa X và Y
Bạn có thể áp dụng 2 công thức sau:
= COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”=10″) đếm xem có bao nhiêu số lớn hơn 5 nhỏ hơn 10 trong dãy dữ liệu C2: C10.
=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) công thức đếm có bao nhiêu số trong phạm vi từ 5 đến 10 trong phạm vi dữ liệu C2: C10, bao gồm cả 5 và 10.
Trong đó:
Công thức đầu tiên được sử dụng để đếm các số lớn hơn giới hạn dưới
Công thức thứ 2 loại bỏ những số lớn hơn giá trị giới hạn trên.
Do đó, kết quả thu được nằm trong khoảng giá trị đã nhận trước đó.
Cách sử dụng tham chiếu ô trong công thức COUNTIFS.
Ví dụ: Sử dụng bảng dữ liệu sau, tính toán đơn đặt hàng cho “Apples” lên tới > 200$
Bạn áp dụng công thức sau:
= COUNTIFS($A$2:$A$11,”Táo”,$B$2:$B$11,”>200″)
Trong đó:
criteria_range1 trong A2 : A11
criteria_range2 trong B2: B11
Lưu ý việc sử dụng các giá trị tuyệt đối trong criteria và criteria_range để công thức luôn chính xác khi được sao chép sang các ô khác.
2.4 Cách sử dụng COUNTIFS với các ký tự đại diện
Bạn có thể sử dụng các ký tự đại diện trong EXCEL như sau:
?: được sử dụng để đếm các ô bắt đầu hoặc kết thúc bằng một số ký tự được chỉ định.
*: Nó được sử dụng để đếm các ô có chứa một từ hoặc ký tự cụ thể trong ô.
Ví dụ: nếu bạn có danh sách các dự án trong cột A. Bạn muốn biết có bao nhiêu dự án đã được ký kết, tức là Cột B chứa tên của những người. Trong cột D, hãy thêm điều kiện END DATE thứ hai.
= COUNTIFS(B2:B10,”*”,D2:D10,”<>””&”)
Lưu ý rằng bạn không thể sử dụng ký tự đại diện trong tiêu chí thứ hai vì cột D chứa kiểu dữ liệu ngày tháng và không phải dạng text. Vì vậy, nó sử dụng các tiêu chí “<>” &”” để tìm ô chứa dữ liệu.
Trên đây là các hàm đếm có điều kiện và cách sử dụng các hàm đếm này trong Excel thông dụng nhất để dễ dàng thống kê và phân tích dữ liệu một cách hiệu quả, từ đó nâng cao hiệu quả công việc tối đa.
