Trong bài viết này, Học Excel Online sẽ lí giải cách thực hiện công thức Hàm COUNTIFS và áp dụng Hàm COUNTIF trong Excel dựa bên trên thuật xúc tích AND và OR. Bạn sẽ tìm thấy tương đối nhiều ví dụ với các kiểu dữ liệu khác biệt – số, ngày tháng, text cùng ô cất dữ liệu.
Bạn đang xem: Hàm đếm giá trị có điều kiện
Hàm COUNTIFS – phương pháp 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 minh phạm vi trước tiên á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 xác minh 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 đang hiển thị những đối số của hàm ngay lúc bạn bước đầu gõ. Đối số được nhập vào hiện nay tại sẽ được tô đậm.
COUNTIFS – đa số điều cần lưu giữ ý:
Bạn có thể sử dụng hàm COUNTIFS để đếm những ô vào một phạm vi đơn lẻ với một đk duy nhất cũng như trong các vùng dữ liệu với khá nhiều điều kiện. Vào trường hợp trang bị hai, chỉ đông đảo ô 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 có 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 hồ hết vùng dữ liệu liền kề và 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 cách xử trí nó như một giá 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 chuẩn – vết hoa thị (*) với dấu chấm hỏi (?).Cách thực hiện hàm COUNTIFS và COUNTIF trong excel với rất nhiều tiêu chí:
Cách đếm ô tính cùng với nhiều tiêu chí (AND logic):
Công thức 1. Bí quyết COUNTIFS 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ư trong hình mặt dưới. Bạn muốn biết số hàng tồn kho (giá trị cột B to hơn 0) tuy thế 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 cách sử dụng phương pháp này:
= COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”)
Kết quả nhận ra là 2 (“Cherries” với “Lemons”):
Công thức 2. Cách làm COUNTIFS với các tiêu chuẩn giống nhau
Khi bạn có nhu cầu đếm các mục 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 giải pháp riêng lẻ.
Ví dụ: đó là công thức đúng để đếm những mục cất 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ó giá trị “0” trong cả hai cột.
Sử dụng một công thức đơn giản hơn với cùng 1 criteria_range duy tuyệt nhất như COUNTIFS(B2: C7, “= 0”) sẽ mang lại tác dụng khác – tổng số các ô tính trong khoảng B2:C7 cất 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ư bạn đã thấy trong lấy ví dụ như trên, việc đếm các ô 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ó phong cách 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 các tiêu chuẩn được chỉ định là TRUE, tức thị dựa trên xúc tích OR thì sao? chú ý chung, có hai cách để thực vấn đề này – 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 mặt dưới, trả sử bạn có nhu cầu tính những đơn mua hàng với tâm lý “Cancelled” và “Pending“, chúng ta bạn chỉ hoàn toàn có thể viết 2 công thức COUNTIF bình 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 phù hợp mỗi hàm đựng được nhiều hơn một điều kiện, thực hiện COUNTIFS thay vày COUNTIF. Ví dụ: nhằm đếm số đơn mua hàng “Cancelled” và “Pending” đối 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 trường hợp bạn cần thiết ập các tiêu chí, biện pháp tiếp cận trên chưa hẳn là giải pháp tối ưu bởi bí quyết của các bạn sẽ quá nhiều năm dòng. Để thực hiện 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 người tiêu dùng trong mảng và cung ứng mảng đó đến criteria của hàm COUNTIFS. Để đếm tổng, phối kết 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 chúng tôi, để đếm các đơn mua 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 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 đơn hàng “Apples” trong tinh thần “Cancelled”, “Pending”, “In transit”, sử 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 tầm số
Để tra cứu ra gồm bao nhiêu số giữa 5 với 10 (không bao gồm 5 và 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 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.
Xem thêm: Quy Trình Quản Trị Thương Hiệu Là Gì? Tài Liệu Quy Trình Quản Trị Thương Hiệu Chọn Lọc
=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – cách làm đếm có bao nhiêu số trong khúc từ 5 mang lại 10 trực thuộc vùng dữ iệu C2:C10, bao hàm cả 5 với 10.
Cách sử dụng tham chiếu ô trong bí quyết COUNTIFS
Khi sử dụng các toán tử lô ghích như “>”, “=” cùng với các tham chiếu ô trong phương pháp COUNTIFS, lưu ý hãy đặt các toán tử trong ngoặc kép cùng thêm vết (&) trước tham chiếu ô để thiết kế một chuỗi văn bản.
Trong tập tài liệu mẫu bên dưới đây, hãy tính các đơn hàng “Apples” với số tiền to hơn $200. Với criteria_range1 vào A2: A11 và criteria_range2 trong B2: B11, chúng ta 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ó thể nhập những giá trị tiêu chí và các ô tốt nhất định, lấy ví dụ F1 và F2, sau đó tham chiếu đầy đủ ô đó trong bí quyết 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 để công thức vẫn đúng khi sao chép sang các ô tính khác.
Cách thực hiện COUNTIFS với những ký từ bỏ đại diện:
Trong bí quyết Excel, chúng ta 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 ngẫu nhiên ký tự đối chọi nào, thực hiện nó để đếm các ô bắt đầu hoặc xong bằng một vài ký tự nhất định.
Dấu hoa thị (*) – khớp với bất kỳ dãy ký kết tự nào, chúng ta dùng nó để đếm ô cất một từ rõ ràng hoặc một k1 tự vào ô.
Lưu ý. Nếu bạn muốn đếm những ô đựng dấu hỏi hoặc giấu hoa thị, gõ dấu xẻ (~) trước vết hoa thị hoặc có thể dấu chấm hỏi.
Giả sử chúng ta có danh sách những dự án trong cột A. Bạn muốn biết gồm bao nhiêu dự án đã cam kết kết, có nghĩa là cột B gồm chứa thương hiệu 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 thay mặt trong tiêu chí thứ 2 bởi cột D đựng kiểu tài liệu ngày mon chứ chưa phải dạng text. Đó là vì sao tại sao bạn sử dụng tiêu chuẩn “”&”” nhằm tìm ô cất 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 hạn cụ thể
Để đếm ngày vào một khoảng thời gian cụ thể, bạn cũng có thể sử dụng phương pháp COUNTIFS với hai tiêu chí 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 trên ngày hiện tại tại
Bạn rất có thể sử dụng hàm TODAY kết phù hợp với COUNTIF để tính các ngày dựa vào ngày hôm nay.
Ví dụ: phương pháp COUNTIF sau với nhì vùng dữ liệu và hai tiêu chí sẽ cho chính mình biết tất cả bao nhiêu thành 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ó thể đựng nhiều biến. Ví dụ: bạn cũng có thể tinh chỉnh nó nhằm đếm gồm bao nhiêu sản phẩm được sở hữu hơn một tuần trước và không giao hàng:
=COUNTIFS(C2:C9,””&TODAY())
Như vậy với bài viết này, h3qvn.com.hocexecl.online đã share cũng như chỉ dẫn cho chúng ta một cách thực hiện công thức CountIFS và CountIF vào Excel một cách cụ thể nhất, ví như có ngẫu nhiên 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 để cửa hàng chúng tôi có thể giải quyết mọi thắc mắc của người sử dụng một bí quyết sớm nhất.