Trong bài viết này, các bạn sẽ được lý giải cách sử dụng hàm đếm con số COUNTIF cùng COUNIFS dựa trên thuật xúc tích và ngắn gọn AND với OR. Bạn sẽ tìm thấy tương đối nhiều ví dụ với những kiểu dữ liệu khác biệt – số, ngày tháng, text với ô cất dữ liệu.

Bạn đang xem: Cách dùng hàm đếm có nhiều điều kiện

CÔNG THỨC HÀM COUNTIFS

=COUNTIFS (Criteria_range1, Criteria1, …)

Criteria_range1 – xác minh phạm vi thứ nhất áp dụng điều kiện trước 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 chuẩn này xác định những ô tính nào sẽ tiến hành đếm và có thể biểu diễn là 10, “Bạn rất có thể sử dụng hàm COUNTIFS để đếm các ô trong một phạm vi riêng lẻ với một điều kiện duy nhất cũng tương tự trong những vùng dữ liệu với khá nhiều điều kiện. Vào trường hợp máy hai, chỉ gần như ô tính đáp ứng tất cả các điều kiện bắt đầu được đếm.Mỗi vùng dữ liệu bổ sung cập nhật phải gồm cùng số hàng với cột như vùng dữ liệu đầu tiên (đối số Criteria_range1).Có thể đếm đều vùng tài liệu liền kề với không tức thời kề.Nếu tiêu chuẩn là một tham chiếu mang lại một ô trống, hàm COUNTIFS giải pháp xử lý nó như một giá bán trị bằng không.Bạn có thể sử dụng các Wildcard characters trong các tiêu chí – lốt hoa thị (*) cùng 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ách làm COUNTIFS cùng với các tiêu chuẩn khác nhau

Giả sử chúng ta có một danh sách sản phẩm như trong hình mặt dưới. Bạn muốn biết số sản phẩm tồn kho (giá trị cột B lớn hơn 0) cơ mà chưa bán được (giá trị cột C bởi 0).

Nhiệm vụ hoàn toàn có thể được thực hiện bằng phương pháp sử dụng công thức này:

= COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”)

Kết quả nhận ra 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 tuyệt nhất như COUNTIFS(B2: C7, “= 0”) sẽ có lại công dụng khác – tổng số những ô tính trong tầm B2:C7 đựng 0 (trong lấy một ví dụ này là 4).

Công thức 2. Phương pháp COUNTIFS cùng với các tiêu chí giống nhau

Khi bạn có nhu cầu đếm các mục gồm các tiêu chuẩn giống nhau, các bạn vẫn phải cung cấp mỗi cặp criteria_range / criteria một bí quyết riêng lẻ.

Ví dụ: đây là công thức đúng để đếm những mục cất 0 cả trong cột B cùng 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ưng chỉ có “Grapes” có mức giá trị “0” vào cả hai cột.

*

CÁCH ĐẾM SỐ Ô TÍNH VỚI NHIỀU TIÊU CHÍ (OR LOGIC)

Như các bạn đã thấy trong ví dụ như trên, vấn đề đếm các ô tính thỏa mãn nhu cầu tất cả các tiêu chuẩn chỉ định rất dễ dàng chính vì hàm COUNTIFS được thiết kế để chuyển động theo biện pháp này.

Nhưng nếu bạn muốn đếm các ô có ít nhất 1 trong các tiêu chuẩn được hướng dẫn và chỉ định là TRUE, tức là dựa trên xúc tích và ngắn gọn OR thì sao? chú ý chung, có hai cách để thực vấn đề đó – bằng cách thêm một trong những công thức COUNTIF hoặc áp dụng công thức SUM COUNTIFS với hằng số mảng.

Công thức 1. Thêm nhị hoặc nhiều công thức COUNTIF hoặc COUNITFS

Trong bảng bên dưới, giả sử bạn có nhu cầu tính các đơn đặt hàng với tâm lý “Cancelled” và “Pending“, các bạn chỉ hoàn toàn có thể viết 2 bí quyết COUNTIF thông thường và thừa nhận kết quả:

=COUNTIF($C$2:$C$11,”Cancelled”)+COUNTIF($C$2:$C$11,”Pending”)

*

Trong trường đúng theo mỗi hàm chứa đựng nhiều hơn một điều kiện, sử dụng COUNTIFS thay vì COUNTIF. Ví dụ: nhằm đếm số đối chọi đặ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Ố vào KHOẢNG CỤ THỂ

Công thức 1. Hàm COUNTIFS đếm các ô trong khoảng số

Để tìm ra bao gồm bao nhiêu số giữa 5 cùng 10 (không bao hàm 5 cùng 10) được chứa trong những ô 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 tất cả bao nhiêu số to hơn 5 nhỏ dại hơn 10 trong vùng dữ liệu C2:C10.

Xem thêm: Máy Sủi Oxy Tích Điện Jebo 9950 Cho Hồ Cá, Máy Sục Khí Oxy 2 Vòi Tích Điện Jebo 9950

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – công thức đếm bao gồm bao nhiêu số trong khúc từ 5 mang đến 10 trực thuộc vùng dữ iệu C2:C10, bao hàm cả 5 và 10.

Cách thực hiện tham chiếu ô trong phương pháp COUNTIFS

Khi sử dụng các toán tử ngắn gọn xúc tích như “>”, “=” thuộc với những tham chiếu ô trong công thức COUNTIFS, lưu ý hãy đặt những toán tử vào ngoặc kép cùng thêm lốt (&) trước tham chiếu ô để gây ra một chuỗi văn bản.

Trong tập dữ liệu mẫu bên dưới đây, hãy tính các giao dịch “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ũng có thể nhập các giá trị tiêu chuẩn và hầu hết ô duy nhất định, ví dụ F1 với F2, tiếp đến tham chiếu hầu như ô đó trong công thức của bạn:

=COUNTIFS($A$2:$A$11,$F$1,$B$2:$B$11,”>&$F$2)

Chú ý áp dụng giá trị tuyệt đối trong criteria và criteria_range để bí quyết vẫn đúng khi xào nấu sang những ô tính khác.

 

CÁCH SỬ DỤNG COUNTIFS VỚI CÁC KÝ TỰ ĐẠI DIỆN

Trong phương pháp Excel, chúng ta cũng 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ự đối chọi nào, sử dụng nó nhằm đếm những ô ban đầu hoặc xong bằng một số trong những ký tự tuyệt nhất định.

Dấu hoa thị (*) – khớp với bất kỳ dãy cam kết tự nào, các bạn dùng nó nhằm đếm ô cất một từ cụ thể hoặc một k1 tự trong ô.

Lưu ý. Nếu bạn có nhu cầu đếm những ô chứa dấu hỏi hoặc dấu hoa thị, gõ dấu bửa (~) trước dấu hoa thị hoặc dấu chấm hỏi.

Giả sử bạn có danh sách những dự án trong cột A. Bạn muốn biết có bao nhiêu dự án công trình đã ký kết, tức là cột B tất cả chứa tên người. Hãy thêm một điều kiện thứ nhị – End date trong cột D.

=COUNTIFS(B2:B10,”*”,D2:D10,””&””))

Lưu ý, chúng ta không thể áp dụng ký tự đại diện trong tiêu chí thứ 2 vì chưng cột D đựng kiểu tài liệu ngày tháng chứ không hẳn dạng text. Đó là vì sao tại sao bạn sử dụng tiêu chí “”&”” để search ô 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 vào một khoảng thời gian cụ thể

Để đếm ngày vào một khoảng thời gian cụ thể, chúng ta 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.

Ví dụ 3. Đếm ngày với khá nhiều điều kiện dựa vào ngày hiện tại

Bạn có thể sử dụng hàm =TODAY() kết hòa 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 nhị vùng dữ liệu và hai tiêu chí sẽ cho mình biết gồm bao nhiêu thành phầm đã được mua nhưng chưa giao hàng: