Hướng Dẫn Cách Dùng Hàm Countif Và Countifs Với Nhiều Điều Kiện

Trong nội dung bài viết này, Học Excel Online sẽ khuyên bảo cách thực hiện công thức Hàm COUNTIFS và thực hiện Hàm COUNTIF vào Excel dựa bên trên thuật súc tích AND và OR. Bạn sẽ tìm thấy không ít ví dụ với những kiểu dữ liệu khác biệt – số, ngày tháng, text với ô chứa dữ liệu.

Bạn đang xem: Cách dùng hàm countif và countifs với nhiều điều kiện

Hàm COUNTIFS – công thức và phương pháp sử dụng

Công thức hàm COUNTIFS như sau:

=COUNTIFS (criteria_range1, criteria1, …)

criteria_range1 – xác định phạm vi trước tiên áp dụng điều kiện thứ nhất (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, “

Trong thực tế, bạn chưa phải thuộc lòng cú pháp của hàm COUNTIFS. Microsoft Excel vẫn hiển thị những đối số của hàm ngay trong khi bạn ban đầu gõ. Đối số được nhập vào hiện nay tại sẽ tiến hành tô đậm.

COUNTIFS – đông đảo điều cần lưu lại ý:

Bạn có thể sử dụng hàm COUNTIFS nhằm đếm các ô vào một phạm vi hiếm hoi với một điều kiện duy nhất tương tự như trong các vùng dữ liệu với nhiều điều kiện. Vào trường hợp đồ vật hai, chỉ những ô tính đáp ứng toàn bộ 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 tất cả cùng số hàng và cột như vùng dữ liệu thứ nhất (đối số criteria_range1).Có thể đếm những vùng tài liệu liền kề và không liền kề.Nếu tiêu chuẩn là một tham chiếu cho một ô trống, hàm COUNTIFS cách xử lý nó như một giá chỉ trị bằng không (0).Bạn có thể sử dụng những wildcard characters trong các tiêu chuẩn – vệt hoa thị (*) cùng dấu chấm hỏi (?).

Cách thực hiện hàm COUNTIFS và COUNTIF trong excel với tương đối nhiều tiêu chí:

Cách đếm ô tính cùng với nhiều tiêu chuẩn (AND logic):

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

Giả sử chúng ta có một danh sách thành phầm như trong hình bên dưới. Bạn có nhu cầu biết số sản phẩm tồn kho (giá trị cột B lớn hơn 0) dẫu vậy chưa bán được (giá trị cột C bằng 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 được là 2 (“Cherries” và “Lemons”):

Công thức 2. Bí quyết COUNTIFS với các tiêu chí giống nhau

Khi bạn có nhu cầu đếm những mục có các tiêu chuẩn giống nhau, chúng ta 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 đựng 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ày chỉ gồm “Grapes” có mức giá trị “0” vào cả hai cột.

Sử dụng một công thức dễ dàng và đơn giản hơn với cùng 1 criteria_range duy độc nhất như COUNTIFS(B2: C7, “= 0”) sẽ sở hữu 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 ví dụ như này là 4).

Cách đếm số ô tính với nhiều tiêu chuẩn (OR logic):

Như các bạn đã thấy trong ví dụ trên, việc đếm những ô 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 dàng chính vì hàm COUNTIFS có phong cách thiết kế để hoạt động theo bí quyết này.

Nhưng nếu bạn có nhu cầu đếm các ô có ít nhất một trong những các tiêu chí được hướng đẫn là TRUE, nghĩa là dựa trên xúc tích OR thì sao? quan sát chung, gồm hai cách để thực điều đó – 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ách làm COUNTIF hoặc COUNITFS

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

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

Trong trường hòa hợp mỗi hàm đựng nhiều hơn một điều kiện, thực hiện COUNTIFS thay do COUNTIF. Ví dụ: nhằm đếm số đơn mua hàng “Cancelled” và “Pending” đối với “Apples”, thực hiện 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ông thức 2. Công thức SUM COUNTIFS với hằng mảng

Trong trường hợp bạn quan trọng ập những tiêu chí, biện pháp tiếp cận trên không phải là phương pháp tối ưu bởi công thức của bạn sẽ quá nhiều năm dòng. Để thực hiện các phép tính tựa như trong một công thức bé dại gọn hơn, hãy liệt kê toàn bộ các tiêu chí của khách hàng trong mảng và cung ứng mảng đó đến criteria của hàm COUNTIFS. Để đếm tổng, phối hợp hàm COUNTIFS phía bên trong hàm SUM như sau:

=SUM (COUNTIFS (range, “criteria1”, “criteria2”, “criteria3”, …))

Trong bảng chủng loại của chúng tôi, nhằm đếm những đơn đặt hàng có tinh thần “Cancelled“, “Pending” hoặc “In transit“, bí quyết sẽ như sau:

=SUM(COUNTIFS($C$2:$C$11,“Cancelled”,”Pending”,”In Transit”))

Tương tự, bạn có thể đếm các ô dựa trên hai hoặc nhiều hơn thế nữa các cặp criteria_range/criteria. Ví dụ: nhằm biết con số đơn đặt hàng “Apples” trong tâm lý “Cancelled”, “Pending”, “In transit”, áp dụng công thức này:

=SUM(COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,“Cancelled”,”Pending”,”In transit”))

Cách đếm số trong khoảng cụ thể:

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

Để tìm kiếm ra gồm bao nhiêu số thân 5 cùng 10 (không bao gồm 5 cùng 10) được chứa trong những ô C2 mang lại 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.

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – bí quyết đếm tất cả bao nhiêu số trong khúc từ 5 cho 10 thuộc vùng dữ iệu C2:C10, bao gồm cả 5 cùng 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ử xúc tích và ngắn gọn như “>”, “=” cùng với những tham chiếu ô trong phương pháp COUNTIFS, xem xét hãy đặt các toán tử vào ngoặc kép và thêm lốt (&) trước tham chiếu ô để kiến thiết một chuỗi văn bản.

Trong tập tài liệu mẫu 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 cùng criteria_range2 trong B2: B11, bạn cũng có thể sử dụng công thức:

=COUNTIFS($A$2:$A$11,”Táo”,$B$2:$B$11,”>200″)

Hoặc, chúng ta cũng có thể nhập các giá trị tiêu chuẩn và đa số ô độc nhất định, lấy ví dụ như F1 với F2, tiếp đến tham chiếu gần như ô đó trong cách làm 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 để bí quyết vẫn đúng khi xào luộc sang những ô tính khác.

Cách áp dụng COUNTIFS với các ký từ đại diện:

Trong bí quyết Excel, bạn cũng có thể sử dụng các ký tự thay mặ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 ô bước đầu hoặc kết thúc bằng một số trong những ký tự độc 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 ô chứa một từ cụ thể hoặc một k1 tự trong ô.

Lưu ý. Nếu bạn muốn đếm những ô cất dấu hỏi hoặc có thể dấu hoa thị, gõ dấu bổ (~) trước lốt hoa thị hoặc che dấu chấm hỏi.

Giả sử bạn có danh sách các dự án vào cột A. Bạn muốn biết gồm bao nhiêu dự án công trình đã cam kết kết, có nghĩa là cột B gồm 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 ý, chúng ta không thể sử dụng ký tự thay mặt trong tiêu chí thứ 2 vị cột D cất kiểu dữ liệu ngày mon chứ không phải dạng text. Đó là lý do tại sao các bạn sử dụng tiêu chí “”&”” nhằm tìm ô chứa dữ liệu.

COUNTIFS cùng COUNTIF cùng với nhiều tiêu chuẩn 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ể, bạn có thể sử dụng công thức COUNTIFS với hai tiêu chí hoặc phối hợp hai hàm COUNTIF cùng với nhau.

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

Bạn rất có thể sử dụng hàm TODAY kết hợp với COUNTIF để tính những ngày dựa trên ngày hôm nay.

Xem thêm: Xem Phim Đoạt Tình Thái Lan, Sctv Tổng Hợp, Phim Doat Tinh Thai Lan

Ví dụ: phương pháp COUNTIF sau với nhì vùng dữ liệu và hai tiêu chuẩn sẽ cho chính mình biết có bao nhiêu thành phầm đã được download nhưng không giao hàng:

=COUNTIFS(C2:C9,””&TODAY())

Công thức này có thể chứa nhiều biến. Ví dụ: chúng ta có thể tinh chỉnh nó nhằm đếm có bao nhiêu thành phầm được tải hơn một tuần trước đó và không giao hàng: