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