Trong bài viết này, Học Excel Online sẽ gợi ý cách áp dụng công thức Hàm COUNTIFS và thực hiện Hàm COUNTIF trong Excel dựa trên thuật xúc tích AND 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 biệt – số, ngày tháng, text và ô đựng dữ liệu.

Bạn đang xem: Hàm đếm có điều kiện


Hàm COUNTIFS – bí quyết và biện 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 đầu tiên (criteria1), bắt buộc.

criteria1 – đặt đk 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ẽ tiến hành đếm và hoàn toàn có thể biểu diễn là 10, “

*

*

Trong thực tế, bạn chưa hẳn 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 khi bạn bắt đầu gõ. Đối số được nhập vào hiện tại sẽ được tô đậm.

*

COUNTIFS – số đông điều cần lưu ý:

Bạn có thể sử dụng hàm COUNTIFS để đếm những ô trong một phạm vi riêng biệt với một đk 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 sản phẩm công nghệ hai, chỉ số đô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 phải tất cả cùng số hàng và cột như vùng dữ liệu trước tiên (đối số criteria_range1).Có thể đếm các vùng dữ liệu liền kề không liền kề.Nếu tiêu chí là một tham chiếu mang lại 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 chí – vệt hoa thị (*) cùng dấu chấm hỏi (?).

Cách áp dụng hàm COUNTIFS với 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. Công thức COUNTIFS cùng với các tiêu chí khác nhau

Giả sử bạn có một danh sách thành phầm như vào hình mặt dưới. Bạn có nhu cầu biết số mặt hàng tồn kho (giá trị cột B lớn hơn 0) nhưng chưa bán tốt (giá trị cột C bởi 0).

Nhiệm vụ rất có thể được thực hiện bằng cách sử dụng cách làm này:

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

Kết quả nhận được là 2 (“Cherries” cùng “Lemons”):

*

Công thức 2. Cách làm COUNTIFS cùng với các tiêu chí giống nhau

Khi bạn có nhu cầu đếm những mục gồm các tiêu chí giống nhau, bạn vẫn phải hỗ trợ 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ả vào cột B với cột C:

= COUNTIFS ($B$2: $B$7, “= 0”, $C$2: $C$7, “= 0”)

Công thức COUNTIFS này trả về 1 bởi chỉ gồm “Grapes” có mức giá trị “0” trong cả hai cột.

*

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

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

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

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

Trong bảng mặt dưới, mang sử bạn có nhu cầu tính các đơn đặt đơn hàng với tâm trạng “Cancelled” cùng “Pending“, bạn bạn chỉ rất có thể viết 2 cách làm COUNTIF thông thường và nhậ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 nhiều hơn một điều kiện, áp dụng COUNTIFS thay bởi vì COUNTIF. Ví dụ: nhằm đếm số đơn đặt hàng “Cancelled”  và “Pending” đối cùng 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ách làm SUM COUNTIFS cùng với hằng mảng

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

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

Trong bảng mẫu mã của bọn chúng tôi, nhằm đếm những đơn mua hàng có tâm trạng “Cancelled“, “Pending” hoặc “In transit“, phương pháp sẽ như sau:

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

*

Tương tự, bạn cũng có thể đếm các ô dựa vào hai hoặc nhiều hơn nữa các cặp criteria_range/criteria. Ví dụ: nhằm biết số lượng đơ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 các ô trong vòng số

Để tìm ra gồm bao nhiêu số giữa 5 cùng 10 (không bao hàm 5 với 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ố lớn hơn 5 bé dại hơn 10 trong vùng tài liệu C2:C10.

Xem thêm: Cách Chơi Game Đua Xe Zing Speed Trên Máy Tính, Laptop, Mẹo Chơi Zing Speed Mobile Luôn Thắng

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – cách làm đếm có bao nhiêu số trong đoạn từ 5 đến 10 thuộc vùng dữ iệu C2:C10, bao hàm cả 5 với 10.

Cách áp dụng 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 những toán tử vào ngoặc kép với thêm vệt (&) trước tham chiếu ô để chế tạo 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 to hơn $200. Cùng với criteria_range1 vào A2: A11 cùng criteria_range2 trong B2: B11, chúng ta 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ó thể nhập các giá trị tiêu chuẩn và đông đảo ô tốt nhất định, lấy ví dụ F1 cùng F2, tiếp nối tham chiếu những ô kia trong cách làm 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ông thức vẫn đúng khi xào nấu sang những ô tính khác.

*

Cách sử dụng COUNTIFS với những ký tự đại diện:

Trong công thức Excel, bạn có thể sử dụng những ký tự đại diện sau đây:

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

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

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

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

*

COUNTIFS với COUNTIF cùng 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 trong một khoảng thời hạn cụ thể, chúng ta cũng có thể sử dụng bí quyết COUNTIFS với hai tiêu chuẩn hoặc phối 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 nay tại

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

Ví dụ: cách làm COUNTIF sau với nhì vùng tài liệu và hai tiêu chí sẽ cho bạn biết tất cả bao nhiêu sản phẩm đã được thiết lập nhưng không giao hàng:

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

*

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

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

Như vậy với bài viết này, nuhoangthoitrang.vn.hocexecl.online đã chia sẻ cũng như lý giải cho các bạn một cách thực hiện công thức CountIFS với 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 comment ngay ở mặt dưới nội dung bài viết để cửa hàng chúng tôi có thể giải quyết mọi thắc mắc của bạn một bí quyết sớm nhất.