Trong nội dung bài viết này, các bạn sẽ được lí giải cách áp dụng hàm đếm số lượng COUNTIF cùng COUNIFS dựa trên thuật xúc tích AND cùng OR. Các bạn sẽ tìm thấy không hề ít ví dụ với các kiểu dữ liệu khác nhau – số, ngày tháng, text cùng ô cất dữ liệu.
Bạn đang xem: Hàm đếm có điều kiện trong khoảng
CÔNG THỨC HÀM COUNTIFS
=COUNTIFS (Criteria_range1, Criteria1,
Criteria_range1 – xác minh phạm vi thứ nhất á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 chí này xác định những ô tính nào sẽ tiến hành đếm và có thể biểu diễn là 10, “Bạn có thể sử dụng hàm COUNTIFS nhằm đếm các ô vào một phạm vi lẻ tẻ với một đk duy nhất cũng giống như trong nhiều vùng dữ liệu với nhiều điều kiện. Vào trường hợp trang bị hai, chỉ mọi ô tính đáp ứng tất cả những điều kiện new được đếm.Mỗi vùng dữ liệu bổ sung cập nhật phải 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 hầu như vùng dữ liệu liền kề với không ngay tắp lự kề.Nếu tiêu chuẩn là một tham chiếu mang lại một ô trống, hàm COUNTIFS cách xử trí nó như một giá chỉ trị bằng không.Bạn rất có thể sử dụng những Wildcard characters vào các tiêu chí – lốt hoa thị (*) cùng dấu chấm hỏi (?).
CÁCH ĐẾM Ô TÍNH VỚI NHIỀU TIÊU CHÍ (AND LOGIC)
Công thức 1. Bí quyết COUNTIFS với các tiêu chuẩn khác nhau
Giả sử bạn có một danh sách thành 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 lớn hơn 0) tuy vậy chưa bán tốt (giá trị cột C bằng 0).
Nhiệm vụ rất có thể được thực hiện bằng cách sử dụng bí quyết này:
= COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”)
Kết quả cảm nhận là 2 (“Cherries” và “Lemons”)
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 vô nhị như COUNTIFS(B2: C7, “= 0”) sẽ với lại công dụng khác – tổng số các ô tính trong khoảng B2:C7 đựng 0 (trong ví dụ như này là 4).
Công thức 2. Phương pháp COUNTIFS với các tiêu chí giống nhau
Khi bạn muốn đếm các mục tất cả các tiêu chuẩn giống nhau, 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 cất 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 vị chỉ gồm “Grapes” có mức giá trị “0” vào cả nhì cột.
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, việc đếm các ô tính đáp ứng nhu cầu tất cả các tiêu chí chỉ định rất giản đơn dàng bởi vì hàm COUNTIFS có phong cách thiết kế để vận động theo phương pháp 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 chí được hướng đẫn là TRUE, nghĩa là dựa trên xúc tích và ngắn gọn OR thì sao? nhìn chung, tất cả hai phương pháp để thực điều này – bằng phương pháp thêm một trong những công thức COUNTIF hoặc sử dụng công thức SUM COUNTIFS cùng với hằng số mảng.
Công thức 1. Thêm nhị hoặc nhiều phương pháp COUNTIF hoặc COUNITFS
Trong bảng mặt dưới, mang sử bạn muốn tính các đơn mua hàng với trạng thái “Cancelled” và “Pending“, các bạn chỉ hoàn toàn có thể viết 2 cách làm COUNTIF bình thường và dìm 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, sử dụng COUNTIFS thay vì COUNTIF. Ví dụ: nhằm đếm số đối chọi đặ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ÁCH ĐẾM SỐ trong KHOẢNG CỤ THỂ
Công thức 1. Hàm COUNTIFS đếm các ô trong tầm số
Để kiếm tìm ra bao gồm bao nhiêu số thân 5 và 10 (không bao hàm 5 với 10) được chứa trong những ô C2 cho 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 gồm bao nhiêu số to hơn 5 nhỏ tuổi hơn 10 trong vùng dữ liệu C2:C10.
Xem thêm: Điều Cần Biết Về Cõi Âm - Thế Giới Của Người Cõi Âm
=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 thuộc vùng dữ iệu C2:C10, bao gồm cả 5 và 10.
Cách áp dụng tham chiếu ô trong bí quyết COUNTIFS
Khi sử dụng các toán tử logic như “>”, “=” cùng với những tham chiếu ô trong công thức COUNTIFS, chú ý hãy đặt các toán tử trong ngoặc kép và thêm dấu (&) trước tham chiếu ô để 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 đơn hàng “Apples” với số tiền to hơn $200. Với criteria_range1 trong A2: A11 và criteria_range2 trong B2: B11, bạn 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 chí và các ô tuyệt nhất định, lấy ví dụ như F1 cùng F2, sau đó tham chiếu hầu hết ô đó trong phương pháp 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 và criteria_range để bí quyết vẫn đúng khi xào luộc sang những ô tính khác.
CÁCH SỬ DỤNG COUNTIFS VỚI CÁC KÝ TỰ ĐẠI DIỆN
Trong cách làm Excel, chúng ta 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 bất kỳ ký tự đối kháng nào, thực hiện nó nhằm đếm các ô ban đầu hoặc kết thúc bằng một trong những ký tự tốt nhất định.
Dấu hoa thị (*) – khớp với ngẫu nhiên dãy ký kết tự nào, chúng ta dùng nó nhằm đếm ô đựng một từ cụ thể 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 dấu hoa thị, gõ dấu té (~) trước dấu hoa thị hoặc che dấu chấm hỏi.
Giả sử bạn có danh sách các dự án vào cột A. Bạn có nhu cầu biết tất cả bao nhiêu dự án đã ký kết kết, có nghĩa là cột B bao 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 ý, các bạn không thể sử dụng ký tự thay mặt đại diện trong tiêu chí thứ 2 bởi vì cột D chứa kiểu tài liệu ngày mon chứ không phải dạng text. Đó là tại sao tại sao bạn áp dụng tiêu chí “”&”” để kiếm tìm ô đựng 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 hạn cụ thể
Để đếm ngày trong một khoảng thời hạn cụ thể, bạn có thể sử dụng công thức COUNTIFS 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 khá nhiều điều kiện dựa vào ngày hiện tại
Bạn rất có thể sử dụng hàm =TODAY() kết đúng theo với COUNTIF để tính những ngày dựa trên ngày hôm nay.
Ví dụ: công thức COUNTIF sau với nhị vùng dữ liệu và hai tiêu chí sẽ cho bạn biết tất cả bao nhiêu thành phầm đã được thiết lập nhưng chưa giao hàng:
