Trong nội dung bài viết này, Học Excel Online sẽ lý giải cách áp dụng công thức Hàm COUNTIFS và áp dụng Hàm COUNTIF trong Excel dựa trên thuật xúc tích AND OR. Các bạn sẽ tìm thấy rất nhiều ví dụ với những kiểu dữ liệu khác nhau – số, ngày tháng, text và ô đựng dữ liệu.

Bạn đang xem: Hàm countif kết hợp hàm and


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 đầu tiên á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 chí này xác minh những ô tính nào sẽ được đếm và hoàn toàn có thể biểu diễn là 10, “

*

*

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

*

COUNTIFS – gần như điều cần lưu lại ý:

Bạn hoàn toàn có thể sử dụng hàm COUNTIFS nhằm đếm những ô trong một phạm vi riêng biệt với một điều kiện duy nhất cũng như trong những vùng dữ liệu với nhiều điều kiện. Trong trường hợp thứ hai, chỉ phần lớn ô tính đáp ứng tất cả các điều kiện new được đếm.Mỗi vùng dữ liệu bổ sung phải bao gồm cùng số hàng và cột như vùng dữ liệu đầu tiên (đối số criteria_range1).Có thể đếm mọi vùng tài liệu liền kề không liền kề.Nếu tiêu chuẩn là một tham chiếu mang đến một ô trống, hàm COUNTIFS giải pháp xử lý nó như một giá chỉ trị bởi không (0).Bạn hoàn toàn có thể sử dụng những wildcard characters trong các tiêu chí – vết hoa thị (*) và dấu chấm hỏi (?).

Cách áp dụng hàm COUNTIFS và COUNTIF trong excel với tương đối nhiều tiêu chí:

Cách đếm ô tính với nhiều tiêu chí (AND logic):

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

Giả sử bạn có một danh sách sản phẩm như vào hình bên dưới. Bạn muốn biết số mặt hàng 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 phương pháp này:

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

Kết quả cảm nhận là 2 (“Cherries”“Lemons”):

*

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 muốn đếm những mục tất cả các tiêu chí giống nhau, các bạn vẫn phải hỗ trợ mỗi cặp criteria_range / criteria một phương pháp riêng lẻ.

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

*

Sử dụng một công thức đơn giản và dễ dàng hơn với cùng một criteria_range duy độc nhất vô nhị như COUNTIFS(B2: C7, “= 0”) sẽ sở hữu lại kết quả khác – tổng số các ô tính trong tầm B2:C7 chứa 0 (trong lấy ví dụ này là 4).

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

Như các bạn đã thấy trong ví dụ trên, việc đếm những ô tính đáp ứng tất cả các tiêu chí chỉ định rất dễ dàng dàng chính vì hàm COUNTIFS được thiết kế theo phong cách để vận động theo biện pháp này.

Nhưng nếu bạn muốn đếm những ô tất cả ít nhất một trong những các tiêu chí được chỉ định và hướng dẫn là TRUE, nghĩa là dựa trên xúc tích OR thì sao? chú ý chung, bao gồm hai phương pháp để thực vấn đề này – bằng phương pháp thêm một số 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ách làm COUNTIF hoặc COUNITFS

Trong bảng mặt dưới, trả sử bạn có nhu cầu tính những đơn đặt hàng với tâm lý “Cancelled” với “Pending“, bạn bạn chỉ có thể viết 2 bí quyết 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 thích hợp mỗi hàm chứa đựng nhiều hơn một điều kiện, áp dụng COUNTIFS thay vày COUNTIF. Ví dụ: để đếm số đơn đặt đơn 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ông thức 2. Công thức SUM COUNTIFS với hằng mảng

Trong tình huống bạn quan trọng ập những tiêu chí, phương pháp tiếp cận trên không phải là cách tối ưu bởi cách làm của bạn sẽ quá nhiều năm dòng. Để triển khai 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ê tất cả các tiêu chí của công ty trong mảng và cung cấp mảng đó cho criteria của hàm COUNTIFS. Để đếm tổng, phối hợp hàm COUNTIFS bên trong hàm SUM như sau:

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

Trong bảng chủng loại của bọn chúng tôi, nhằm đếm các đơn đặt hàng có tâm lý “Cancelled“, “Pending” hoặc “In transit“, cách làm sẽ như sau:

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

*

Tương tự, chúng ta cũng có thể đếm những ô dựa vào 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 đơn hàng “Apples” trong tâm trạng “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 vòng cụ thể:

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

Để tìm kiếm ra bao gồm bao nhiêu số thân 5 và 10 (không bao hàm 5 cùng 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ỏ tuổi hơn 10 trong vùng tài liệu C2:C10.

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – cách làm đếm bao gồm bao nhiêu số trong đoạn từ 5 cho 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 cách làm COUNTIFS

Khi sử dụng các toán tử ngắn gọn xúc tích như “>”, “=” thuộc với các tham chiếu ô trong cách làm COUNTIFS, để ý hãy đặt các toán tử vào ngoặc kép với thêm vệt (&) trước tham chiếu ô để xuất bản 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 deals “Apples” cùng với số tiền to hơn $200. Cùng với criteria_range1 vào A2: A11 cùng 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, chúng ta có thể nhập những giá trị tiêu chuẩn và hồ hết ô độc nhất định, lấy ví dụ như F1 với F2, sau đó tham chiếu hầu như ô đó trong phương pháp của bạn:

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

Chú ý thực hiện giá trị tuyệt đối trong criteria criteria_range để cách làm vẫn đúng khi coppy sang các ô tính khác.

*

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

Trong phương pháp Excel, chúng ta có thể sử dụng các ký tự thay mặt sau đây:

Dấu hỏi (?) – khớp với ngẫu nhiên ký tự solo nào, áp dụng nó để đếm những ô bắt đầu hoặc ngừng bằng một số ký tự duy nhất định.

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

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

Giả sử chúng ta có danh sách những dự án trong cột A. Bạn có nhu cầu biết gồm bao nhiêu dự án công trình đã ký kết kết, tức là cột B bao gồm chứa tên người. Hãy thêm một đk thứ nhì – End date trong cột D.

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

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

Để đếm ngày vào một khoảng thời hạn cụ thể, chúng ta cũng có thể sử dụng cách làm COUNTIFS với hai tiêu chí hoặc phối kết hợp hai hàm COUNTIF cùng với nhau.

*

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

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

Xem thêm: Ảnh Các Mẫu Background Trắng Đẹp, Tổng Hợp Background Trắng Đẹp Nhất

Ví dụ: cách làm COUNTIF sau với nhị vùng tài liệu và hai tiêu chí sẽ cho mình biết bao gồm bao nhiêu thành phầm đã được cài đặt nhưng không giao hàng:

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

*

Công thức này còn có thể chứa được nhiều biến. Ví dụ: bạn có thể tinh chỉnh nó để đếm gồm bao nhiêu sản phẩm được thiết lập hơn một tuần trước đó và không giao hàng:

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

Như vậy với nội dung bài viết này, vietmac.com.vn.hocexecl.online đã share cũng như gợi ý cho chúng ta một cách thực hiện công thức CountIFS cùng CountIF vào Excel một cách cụ thể nhất, nếu như có ngẫu nhiên thắc mắc nào chúng ta hãy phản hồi ngay ở mặt dưới nội dung bài viết để shop chúng tôi có thể giải quyết mọi thắc mắc của chúng ta một biện pháp sớm nhất.