Hàm COUNTIF là một hàm rất hữu ích trong việc thống kê, báo cáo số liệu. Vậy nên, nếu bạn chưa biết cách sử dụng hàm này như thế nào thì mời bạn cùng Sforum xem cách ứng dụng hàm đếm có điều kiện qua các ví dụ cụ thể trong bài viết dưới đây nhé!
Hàm COUNTIF là gì?
Hàm COUNTIF là hàm đếm có điều kiện trong Excel. Bạn có thể dùng để đếm ô trùng lặp, đếm dữ liệu. Thường thì hàm đếm số lượng có điều kiện này sẽ dùng với những bảng dữ liệu thống kê, yêu cầu đếm số ô chứa giá trị thỏa mãn điều kiện cho sẵn.Bạn đang xem: Hàm đếm số lượng excel
Công thức hàm đếm có điều kiện
Hàm COUNTIF trong Excel có công thức là =COUNTIF(range;criteria). Trong đó:
Range: Là vùng cần đếm dữ liệu.Criteria: Điều kiện cần đếm. Đối số criteria cần để trong dấu ngoặc kép. Không phân biệt chữ thường hay chữ hoa.Cách sử dụng hàm COUNTIF trong Excel và ví dụ
Mời bạn tìm hiểu cách sử dụng hàm COUNTIF qua các ví dụ tìm kiếm dữ liệu khác nhau.
Ví dụ 1: Dùng hàm COUNTIF tìm kiếm số lượng tên hàng Dưa hấu có trong bảng sau
Công thức như sau: =COUNTIF(B2:B8,”dưa hấu”) rồi nhấn Enter để thực hiện hàm.
Và kết quả sẽ đếm ra 2 giá trị có tên là Dưa hấu.
Ví dụ 2: Vẫn với bảng trên, chúng ta sẽ đếm số lượng mặt hàng lớn hơn 6 kg
Công thức như sau: =COUNTIF(C2:C8,”>6″) rồi nhấn Enter để thực hiện hàm.
Kết quả là sẽ đếm ra được là có 3 mặt hàng có khối lượng lớn 6kg.
Ngoài ra, đối với các trường hợp sau:
Đếm số lượng nhỏ hơn 6kg, thay đổi đối số criteria là “Đếm số lượng lớn hơn hoặc bằng 6kg, thay đổi đối số criteria là “>=6”.Đếm số lượng nhỏ hơn hoặc bằng 6kg, thay đổi đối số criteria là “Ví dụ 3: Dùng hàm COUNTIF tìm số lượng hàng không phải là Dưa hấu trong bảng
Công thức như sau: =COUNTIF(B2:B8,”Dưa hấu”) rồi nhấn Enter để thực hiện hàm. Trong đó “” là ký tự khác
Kết quả đếm được là có 5 mặt hàng không phải dưa hấu.
Ví dụ 4: Dùng hàm COUNTIF tìm kiếm đơn hàng có tên Sầu riêng sử dụng ký tự thay thế
Để đếm mặt hàng bằng cách sử dụng ký tự thay thế thì ở đối số criteria, bạn điền chữ cái đầu của tên mặt hàng và theo sau đó là ký tự hoa *.
*Lưu ý: Cách này chỉ có thể áp dụng khi các mặt hàng có chữ cái đầu khác nhau.
Công thức như sau: =COUNTIF(B2:B8,”S*”) rồi nhấn Enter để thực hiện hàm.
Và đây là kết quả.
Ví dụ 5: Tìm kiếm mặt hàng có tên khác ô B3
Ở đây chúng ta sẽ tìm những mặt hàng có tên khác ô B3 là Xoài bằng cách dùng ký tự “” và “&” trước ô tham chiếu, với cú pháp hàm =COUNTIF(B2:B8,””&B3).
Kết quả sẽ ra số mặt hàng chính xác là 6.
Kết luận cách dùng COUNTIF – Hàm đếm có điều kiện trong Excel
Trên đây là bài viết hướng dẫn bạn cách sử dụng hàm COUNTIF – Hàm đếm số lượng có điều kiện trong Excel. Hy vọng những thông tin này giúp ích cho bạn. Ngoài ra bạn cũng có thể tham khảo thêm nhiều thủ thuật Excel khác tại Sforum.
Trong bài viết này, các bạn sẽ được hướng dẫn cách sử dụng hàm đếm số lượng COUNTIF và COUNIFS dựa trên thuật logic AND và OR. Bạn sẽ tìm thấy rất nhiều ví dụ với các kiểu dữ liệu khác nhau – số, ngày tháng, text và ô chứa dữ liệu.
CÔNG THỨC HÀM COUNTIFS
=COUNTIFS (Criteria_range1, Criteria1,
Criteria_range1 – xác định phạm vi đầu tiên áp dụng điều kiện đầu tiên (criteria1), bắt buộc.
Criteria1 – đặt điều kiện cho dạng 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. Tiêu chí này xác định những ô tính nào sẽ được đếm và có thể biểu diễn là 10, “Bạn có thể sử dụng hàm COUNTIFS để đếm các ô trong một phạm vi đơn lẻ với một điều kiện duy nhất cũng như trong nhiều vùng dữ liệu với nhiều điều kiện. Trong trường hợp thứ hai, chỉ những ô tính đáp ứng tất cả các điều kiện mới được đếm.Mỗi vùng dữ liệu bổ sung phải có cùng số hàng và cột như vùng dữ liệu đầu tiên (đối số Criteria_range1).Có thể đếm những vùng dữ liệu liền kề và không liền kề.Nếu tiêu chí là một tham chiếu đến một ô trống, hàm COUNTIFS xử lý nó như một giá trị bằng không.Bạn có thể sử dụng các Wildcard characters trong các tiêu chí – dấu hoa thị (*) và dấu chấm hỏi (?).
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 danh sách sản phẩm như trong hình bên dưới. Bạn muốn biết số 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ụ 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ả nhận được là 2 (“Cherries” và “Lemons”)
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ẽ mang lại 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).
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ó 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ẻ.
Ví dụ: đâ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 COUNTIFS này trả về 1 vì chỉ có “Grapes” có giá trị “0” trong cả hai cột.
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 đếm các ô tính đáp ứng tất cả các tiêu chí chỉ định rất dễ dàng bởi vì hàm COUNTIFS được thiết kế để hoạt động 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, 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 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 COUNITFS
Trong bảng bên dưới, giả sử bạn muốn tính các đơn đặt hàng với trạng thái “Cancelled” và “Pending“, bạn chỉ 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”)
Trong trường hợp mỗi hàm chứa nhiều hơn một điều kiện, sử dụng COUNTIFS thay vì COUNTIF. Ví dụ: để đếm số đơn đặt hàng “Cancelled” và “Pending” đối với “Apples”, 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”)
CÁCH ĐẾM SỐ TRONG KHOẢNG CỤ THỂ
Công thức 1. Hàm COUNTIFS đếm các ô trong khoảng số
Để tìm ra có bao nhiêu số giữa 5 và 10 (không bao gồm 5 và 10) được chứa trong các ô C2 đến C10, sử dụng công thức này:
=COUNTIFS(C2:C10,”>5″,C2:C10,”=5″,B2:B10,”5″)-COUNTIF(C2:C10,”=10″) – đếm có bao nhiêu số lớn hơn 5 nhỏ hơn 10 trong vùng dữ liệu C2:C10.
=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – công thức đếm có bao nhiêu số trong đoạn từ 5 đến 10 thuộc vùng dữ iệu C2:C10, bao gồm cả 5 và 10.
Cách sử dụng tham chiếu ô trong công thức COUNTIFS
Khi sử dụng các toán tử logic như “>”, “=” cùng với các tham chiếu ô trong công thức COUNTIFS, lưu ý hãy đặt các toán tử trong ngoặc kép và thêm dấu (&) trước tham chiếu ô để xây dựng một chuỗi văn bản.
Trong tập dữ liệu mẫu dưới đây, hãy tính các đơn hàng “Apples” với số tiền lớn hơn $200. Với criteria_range1 trong A2: A11 và criteria_range2 trong B2: B11, bạn có thể sử dụng công thức:
=COUNTIFS($A$2:$A$11,”Táo”,$B$2:$B$11,”>200″)
Hoặc, bạn có thể nhập các giá trị tiêu chí và những ô nhất định, ví dụ F1 và F2, sau đó tham chiếu những ô đó trong công thức của bạn:
=COUNTIFS($A$2:$A$11,$F$1,$B$2:$B$11,”>&$F$2)
Chú ý sử dụng giá trị tuyệt đối trong criteria và criteria_range để công thức vẫn đúng khi sao chép sang các ô tính khác.
CÁCH SỬ DỤNG COUNTIFS VỚI CÁC KÝ TỰ ĐẠI DIỆN
Trong công thức Excel, bạn có thể sử dụng các ký tự đại diện sau đây:
Dấu hỏi (?) – khớp với bất kỳ ký tự đơn nào, sử dụng nó để đếm các ô bắt đầu hoặc kết thúc bằng một số ký tự nhất định.
Dấu hoa thị (*) – khớp với bất kỳ dãy ký tự nào, bạn dùng nó để đếm ô chứa một từ cụ thể hoặc một k1 tự trong ô.
Lưu ý. Nếu bạn muốn đếm các ô chứa dấu hỏi hoặc dấu hoa thị, gõ dấu ngã (~) trước dấu hoa thị hoặc dấu chấm hỏi.
Giả sử 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 đã ký kết, tức là cột B có chứa tên người. Hãy thêm một điều kiện thứ hai – End date trong cột D.
=COUNTIFS(B2:B10,”*”,D2:D10,””&””))
Lưu ý, bạn không thể sử 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 chứ không phải dạng text. Đó là lý do tại sao bạn sử dụng tiêu chí “”&”” để tìm ô chứa dữ liệu.
COUNTIFS VÀ COUNTIF VỚI NHIỀU TIÊU CHÍ NGÀY THÁNG
Ví dụ 1. Đếm ngày trong một khoảng thời gian cụ thể
Để đếm ngày trong một khoảng thời gian cụ thể, bạn có thể sử dụng công thức COUNTIFS với hai tiêu chí hoặc kết hợp hai hàm COUNTIF với nhau.
Xem thêm: Cách Giới Thiệu Bạn Bè Cho Người Khác Trên Facebook, Cách Giới Thiệu Bạn Bè Trên Facebook
Ví dụ 3. Đếm ngày với nhiều điều kiện dựa trên ngày hiện tại
Bạn có thể sử dụng hàm =TODAY() kết hợp với COUNTIF để tính các ngày dựa trên ngày hôm nay.
Ví dụ: công thức COUNTIF sau với hai vùng dữ liệu và hai tiêu chí sẽ cho bạn biết có bao nhiêu sản phẩm đã được mua nhưng chưa giao hàng: