Cách sử dụng hàm countif trong excel
Trong nội dung bài viết này, Học Excel Online sẽ hướng dẫn cách áp dụng công thức Hàm COUNTIFS và sử dụng Hàm COUNTIF trong Excel dựa bên trên thuật ngắn gọn xúc tích AND và OR. Các bạn sẽ tìm thấy không ít ví dụ với những kiểu dữ liệu không giống nhau – số, ngày tháng, text và ô cất dữ liệu.
Bạn đang xem: Cách sử dụng hàm countif trong excel
Hàm COUNTIFS – cách làm và biện pháp sử dụng
Công thức hàm COUNTIFS như sau:
=COUNTIFS (criteria_range1, criteria1,
criteria_range1 – khẳng đị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 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 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ước đầu gõ. Đối số được nhập vào hiện nay tại sẽ tiến hành tô đậm.
COUNTIFS – đầy đủ điều cần lưu giữ ý:
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 lẻ với một đk duy nhất cũng tương tự trong các vùng dữ liệu với khá nhiều điều kiện. Trong trường hợp trang bị hai, chỉ hầu như ô tính đáp ứng toàn bộ 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ới cột như vùng dữ liệu trước tiên (đối số criteria_range1).Có thể đếm rất nhiều 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 xử lý nó như một giá trị bằng không (0).Bạn rất có thể sử dụng những wildcard characters trong các tiêu chuẩn – dấu hoa thị (*) với dấu chấm hỏi (?).Cách thực hiện hàm COUNTIFS với COUNTIF vào 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 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 mặt dưới. Bạn có nhu cầu biết số sản phẩm tồn kho (giá trị cột B to hơn 0) cơ mà chưa bán tốt (giá trị cột C bởi 0).
Nhiệm vụ 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ả nhận ra là 2 (“Cherries” cùng “Lemons”):
Công thức 2. Cách làm COUNTIFS với các tiêu chí giống nhau
Khi bạn muốn đếm các mục bao gồm các tiêu chuẩn giống nhau, các bạn vẫn phải cung ứng mỗi cặp criteria_range / criteria một giải pháp riêng lẻ.
Ví dụ: đấy là công thức đúng nhằm đếm các mục đựng 0 cả vào 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 bởi vì 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 đơn giản dễ dàng hơn với một criteria_range duy độc nhất như COUNTIFS(B2: C7, “= 0”) sẽ có lại hiệu quả khác – tổng số những ô tính trong khoảng B2:C7 đựng 0 (trong lấy một ví dụ 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, 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 đơn giản dàng cũng chính vì hàm COUNTIFS được thiết kế với để chuyển động theo phương pháp này.
Nhưng nếu bạn muốn đếm các ô bao gồm ít nhất một trong những các tiêu chí được chỉ định là TRUE, tức là dựa trên súc tích OR thì sao? quan sát chung, tất cả hai cách để thực điều đó – bằng cách thêm một 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ách làm COUNTIF hoặc COUNITFS
Trong bảng mặt dưới, giả sử bạn có nhu cầu tính các đơn mua hàng với trạng thái “Cancelled” cùng “Pending“, các bạn bạn chỉ có thể viết 2 bí quyết 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 đúng theo mỗi hàm chứa nhiều hơn một điều kiện, sử dụng COUNTIFS thay bởi COUNTIF. Ví dụ: để đếm số đơn đặt 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ách làm SUM COUNTIFS cùng với hằng mảng
Trong tình huống bạn cần thiết ập những tiêu chí, cách tiếp cận trên không hẳn là phương pháp tối ưu bởi cách làm của các bạn sẽ quá dài dòng. Để tiến hành các phép tính giống như trong một công thức nhỏ dại gọn hơn, hãy liệt kê tất cả các tiêu chí của bạn trong mảng và cung cấp mảng đó mang đến criteria của hàm COUNTIFS. Để đếm tổng, phối kết 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 những đơn đặt đơn hàng có tâm lý “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ũng có thể đếm những ô 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 số lượng đơ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 khoảng cụ thể:
Công thức 1. Hàm COUNTIFS đếm những ô trong khoảng số
Để tìm ra có bao nhiêu số giữa 5 cùng 10 (không bao gồm 5 và 10) được chứa trong các ô C2 cho 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ố to 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″) – phương pháp đếm gồm bao nhiêu số trong khúc từ 5 đến 10 nằm trong vùng dữ iệu C2:C10, bao hàm cả 5 cùng 10.
Cách thực hiện tham chiếu ô trong công thức COUNTIFS
Khi sử dụng những toán tử xúc tích và ngắn gọn như “>”, “=” thuộc với những tham chiếu ô trong công thức COUNTIFS, lưu ý hãy đặt những toán tử trong ngoặc kép với thêm dấu (&) trước tham chiếu ô để desgin 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 deals “Apples” với số tiền to hơn $200. Cùng với criteria_range1 trong A2: A11 cùng 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, bạn 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ụ F1 với F2, sau đó tham chiếu các ô kia trong cách làm 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ách làm vẫn đúng khi sao chép sang các ô tính khác.
Cách sử dụng COUNTIFS với những ký tự đại diện:
Trong phương pháp Excel, chúng ta có thể sử dụng những ký tự thay mặt sau đây:
Dấu hỏi (?) – khớp với bất kỳ ký tự đối kháng nào, áp dụng nó để đếm những ô ban đầu hoặc kết thúc bằng một số ký 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ó để đếm ô cất một từ ví dụ hoặc một k1 tự vào ô.
Lưu ý. Nếu bạn muốn đếm các ô đựng dấu hỏi hoặc có thể dấu hoa thị, gõ dấu bổ (~) trước dấu hoa thị hoặc giấu chấm hỏi.
Giả sử các bạn có danh sách những dự án vào cột A. Bạn có nhu cầu biết bao gồm bao nhiêu dự án đã ký 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ứ 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 trong tiêu chuẩn thứ 2 bởi cột D đựng kiểu tài liệu ngày mon chứ chưa hẳn dạng text. Đó là lý do 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 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ể, chúng ta cũng có thể sử dụng công thức COUNTIFS cùng với hai tiêu chuẩn 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 trên ngày hiện tại
Bạn 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 hai vùng tài liệu và hai tiêu chuẩn sẽ cho chính mình biết gồm bao nhiêu thành phầm đã được tải nhưng chưa giao hàng:
=COUNTIFS(C2:C9,””&TODAY())
Công thức này có thể đựng được nhiều biến. Ví dụ: bạn cũng có thể tinh chỉnh nó để đếm có bao nhiêu thành phầm được sở hữu 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, loadingvn.com.hocexecl.online đã chia sẻ cũng như gợi ý cho chúng ta 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 có ngẫu nhiên thắc mắc nào các bạn hãy comment ngay ở mặt 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 doanh nghiệp một giải pháp sớm nhất.
