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

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


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

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

=COUNTIFS (criteria_range1, criteria1, …)

criteria_range1 – khẳng định phạm vi thứ nhất á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 chuẩn này khẳng đị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 hẳn thuộc lòng cú pháp của hàm COUNTIFS. Microsoft Excel vẫn hiển thị các đố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 – hầu như điều cần lưu giữ ý:

Bạn có thể sử dụng hàm COUNTIFS để đếm các ô vào một phạm vi lẻ tẻ với một điều kiện duy nhất tương tự như trong những vùng dữ liệu với rất nhiều điều kiện. Vào trường hợp sản phẩm hai, chỉ các ô 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 bao gồm cùng số hàng cùng cột như vùng dữ liệu thứ nhất (đối số criteria_range1).Có thể đếm phần nhiều 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 xử trí nó như một giá trị bởi 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ị (*) và dấu chấm hỏi (?).

Cách thực hiện hàm COUNTIFS cùng COUNTIF vào excel vớ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. Cách làm COUNTIFS cùng 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ư vào hình bên 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) tuy 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 cách sử dụng công thức này:

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

Kết quả nhận thấy là 2 (“Cherries”“Lemons”):

*

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

Khi bạn muốn đếm những mục tất cả các tiêu chí 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 nhằm đếm các mục chứa 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 bởi chỉ gồm “Grapes” có mức giá trị “0” trong cả nhì cột.

*

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

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

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

Nhưng nếu bạn muốn đếm những ô bao gồm ít nhất 1 trong các các tiêu chuẩn được chỉ định và hướng dẫn là TRUE, nghĩa là dựa trên súc tích OR thì sao? quan sát chung, bao gồm hai phương pháp để thực điều này – bằng cách thêm một vài 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 mặt dưới, đưa sử bạn muốn tính những đơn đặt hàng với tâm lý “Cancelled” cùng “Pending“, bạn bạn chỉ hoàn toàn có thể viết 2 phương pháp 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 nhiều hơn một điều kiện, sử dụng COUNTIFS thay vì COUNTIF. Ví dụ: nhằm đếm số đơn đặt hàng “Cancelled”  và “Pending” đối cùng với “Apples”, áp 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 nhiều tiêu chí, bí quyết tiếp cận trên không hẳn là biện pháp tối ưu bởi công thức của các bạn sẽ quá dài dòng. Để triển khai các phép tính giống như trong một công thức nhỏ tuổi gọn hơn, hãy liệt kê tất cả các tiêu chí của khách hàng trong mảng và cung cấp mảng đó mang lại 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 mẫu của bọn chúng tôi, để đếm các đơ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 những ô dựa vào hai hoặc nhiều hơn thế các cặp criteria_range/criteria. Ví dụ: để biết con số đơn đặt hàng “Apples” trong tâm lý “Cancelled”, “Pending”, “In transit”, thực hiện 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 khoảng số

Để tra cứu ra gồm bao nhiêu số thân 5 và 10 (không bao gồm 5 cùng 10) được chứa trong những ô C2 mang đến C10, thực hiện 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 nhỏ dại hơn 10 vào vùng dữ liệu C2:C10.

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – bí quyết đếm bao gồm bao nhiêu số trong đoạn từ 5 cho 10 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ử súc tích như “>”, “=” cùng với các tham chiếu ô trong công thức COUNTIFS, để ý hãy đặt những toán tử trong ngoặc kép và thêm vệt (&) 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 giao dịch “Apples” với số tiền to hơn $200. Với criteria_range1 trong A2: A11 và criteria_range2 vào 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ũng có thể nhập những giá trị tiêu chuẩn và đầy đủ ô tốt nhất định, lấy một ví dụ F1 cùng F2, tiếp nối tham chiếu mọi ô kia trong bí quyết 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 criteria_range để bí quyết vẫn đúng khi xào nấu sang những ô tính khác.

*

Cách thực hiện 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 thay mặt sau đây:

Dấu hỏi (?) – khớp với ngẫu nhiên ký tự 1-1 nào, thực hiện nó để đếm các ô ban đầu hoặc ngừng bằng một số ký tự tuyệ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ó nhằm đếm ô chứa một từ rõ ràng hoặc một k1 tự vào ô.

Lưu ý. Nếu bạn muốn đếm các ô chứa dấu hỏi hoặc giấu hoa thị, gõ dấu té (~) trước vệt hoa thị hoặc dấu chấm hỏi.

Giả sử chúng ta có danh sách các dự án vào 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 thương hiệu người. Hãy thêm một đk thứ nhì – End date trong cột D.

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

Lưu ý, các bạn không thể sử dụng ký tự đại diện trong tiêu chuẩn thứ 2 vị cột D chứa kiểu tài liệu ngày tháng chứ không hẳn dạng text. Đó là vì sao tại sao chúng ta sử dụng tiêu chuẩn “”&”” để tìm ô cất dữ liệu.

*

COUNTIFS với COUNTIF 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 hạn cụ thể, bạn cũng có thể sử dụng phương pháp COUNTIFS với hai tiêu chuẩn hoặc phối kết hợp hai hàm COUNTIF 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 nay tại

Bạn 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.

Xem thêm:

Ví dụ: phương pháp COUNTIF sau với nhị vùng tài liệu và hai tiêu chí sẽ cho bạn biết bao gồm bao nhiêu sản phẩm đã được cài nhưng chưa giao hàng:

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

*

Công thức này còn có thể đựng nhiều biến. Ví dụ: bạn cũng có thể tinh chỉnh nó để đếm có bao nhiêu sản 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 nội dung bài viết này, loanthehongnhan.vn.hocexecl.online đã chia sẻ cũng như hướng dẫn cho các bạn một cách áp dụng công thức CountIFS cùng CountIF vào Excel một cách chi tiết nhất, nếu có bất kỳ thắc mắc nào các bạn hãy bình luận ngay ở bên dưới bài viết để chúng tôi có thể giải quyết và xử lý mọi thắc mắc của khách hàng một giải pháp sớm nhất.