Trong nội dung bài viết này, Học Excel Online sẽ chỉ dẫn cách sử dụng công thức Hàm COUNTIFS và thực hiện Hàm COUNTIF vào Excel dựa bên trên thuật xúc tích AND và OR. Các bạn sẽ tìm thấy rất nhiều ví dụ với những kiểu dữ liệu khác biệt – số, ngày tháng, text cùng ô đựng dữ liệu.
Bạn đang xem: Hàm đếm có điều kiện trong khoảng
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 thứ nhất áp dụng điều kiện thứ nhất (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 đị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 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 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 – đa số điều cần giữ ý:
Bạn rất có thể sử dụng hàm COUNTIFS để đếm các ô trong một phạm vi riêng biệt với một đk duy nhất tương tự như trong các vùng dữ liệu với nhiều điều kiện. Vào trường hợp sản phẩm công nghệ hai, chỉ phần lớn ô tính đáp ứng tất cả các điều kiện new được đếm.Mỗi vùng dữ liệu bổ sung cập nhật phải bao gồm 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 đều vùng tài liệu liền kề và không liền kề.Nếu tiêu chuẩn là một tham chiếu cho một ô trống, hàm COUNTIFS xử lý nó như một giá bán trị bởi không (0).Bạn có thể sử dụng những wildcard characters trong các tiêu chí – vệt hoa thị (*) cùng 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 với nhiều tiêu chuẩn (AND logic):
Công thức 1. Cách làm COUNTIFS với các tiêu chuẩn khác nhau
Giả sử các bạn có một danh sách sản phẩm như trong hình mặt dưới. Bạn có nhu cầu biết số hàng tồn kho (giá trị cột B lớn hơn 0) nhưng mà chưa bán được (giá trị cột C bởi 0).
Nhiệm vụ rất có thể được thực hiện bằng cách sử dụng cách làm này:
= COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”)
Kết quả nhận ra là 2 (“Cherries” và “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 những mục gồm các tiêu chuẩn giống nhau, chúng ta vẫn phải hỗ trợ mỗi cặp criteria_range / criteria một phương 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 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 do chỉ tất cả “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 1 criteria_range duy nhất như COUNTIFS(B2: C7, “= 0”) sẽ sở hữu lại hiệu quả khác – tổng số những ô tính trong khoảng B2:C7 cất 0 (trong ví dụ này là 4).
Cách đếm số ô tính cùng với nhiều tiêu chuẩn (OR logic):
Như chúng ta đã thấy trong ví dụ như trên, bài toán đếm các ô tính thỏa mãn nhu cầu tất cả các tiêu chuẩn chỉ định rất dễ dàng chính vì hàm COUNTIFS được thiết kế để hoạt động theo biện pháp 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 chuẩn được hướng dẫn và chỉ định là TRUE, tức thị dựa trên xúc tích OR thì sao? quan sát chung, gồm hai cách để thực vấn đề này – bằng cách thêm một số trong những công thức COUNTIF hoặc thực hiện 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 phương pháp COUNTIF hoặc COUNITFS
Trong bảng bên dưới, giả sử bạn có nhu cầu tính các đơn đặt hàng với tinh thần “Cancelled” và “Pending“, các bạn bạn chỉ có thể viết 2 phương pháp COUNTIF bình thường và dấ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 chứa đựng nhiều hơn một điều kiện, áp dụng COUNTIFS thay vì chưng COUNTIF. Ví dụ: nhằm đếm số đơn mua 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. Cách làm SUM COUNTIFS với hằng mảng
Trong tình huống bạn quan trọng ập những tiêu chí, bí quyết tiếp cận trên chưa phải là giải pháp tối ưu bởi bí quyết của bạn sẽ quá nhiều năm dòng. Để thực hiện các phép tính tương tự trong một công thức nhỏ 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 đó 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, nhằm đếm những đơn đặt hàng có tinh thần “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 trên hai hoặc nhiều hơn thế các cặp criteria_range/criteria. Ví dụ: để biết số lượng đơn mua hàng “Apples” trong trạng thái “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 vòng số
Để tìm ra tất cả bao nhiêu số thân 5 với 10 (không bao gồm 5 cùng 10) được chứa trong các ô 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 gồm bao nhiêu số lớn hơn 5 nhỏ tuổi hơn 10 vào vùng dữ liệu C2:C10.
Xem thêm: Bảng Giá Và Lịch Khám Bệnh Viện Đại Học Y Dược Tp Hcm, Đăng Ký Khá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 nằm trong vùng dữ iệu C2:C10, bao gồm cả 5 cùng 10.
Cách áp dụng tham chiếu ô trong công thức COUNTIFS
Khi sử dụng các toán tử ngắn gọn xúc tích như “>”, “=” thuộc với những tham chiếu ô trong cách làm COUNTIFS, để ý hãy đặt những toán tử trong ngoặc kép và thêm vệt (&) trước tham chiếu ô để sản xuất 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” cùng với số tiền lớn hơn $200. Cùng với criteria_range1 vào 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ó thể nhập những giá trị tiêu chí và hầu như ô tốt nhất định, lấy ví dụ như F1 và F2, sau đó tham chiếu số đông ô kia trong phương pháp của bạn:
=COUNTIFS($A$2:$A$11,$F$1,$B$2:$B$11,”>&$F$2)
Chú ý thực hiện giá trị tuyệt đối trong criteria và criteria_range để phương pháp vẫn đúng khi xào nấu 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 cách làm Excel, bạn có thể sử dụng các ký tự thay mặt sau đây:
Dấu hỏi (?) – khớp với ngẫu nhiên ký tự đối chọi nào, sử dụng nó nhằm đếm các ô bước đầ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 bất kỳ dãy ký tự nào, bạn dùng nó để đếm ô đựng 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 ô chứa dấu hỏi hoặc dấu hoa thị, gõ dấu ngã (~) trước lốt hoa thị hoặc dấu chấm hỏi.
Giả sử bạn có danh sách những dự án vào cột A. Bạn muốn biết bao gồm bao nhiêu dự án đã cam kết kết, có nghĩa là cột B bao gồm chứa thương hiệu người. Hãy thêm một đk thứ hai – End date trong cột D.
=COUNTIFS(B2:B10,”*”,D2:D10,””&””))
Lưu ý, các bạn không thể thực hiện ký tự thay mặt đại diện trong tiêu chí thứ 2 do cột D chứa kiểu dữ liệu ngày tháng chứ không phải dạng text. Đó là nguyên nhân tại sao bạn sử dụng tiêu chuẩn “”&”” nhằm tìm ô đựng dữ liệu.
COUNTIFS cùng COUNTIF với nhiều tiêu chí ngày tháng:
Ví dụ 1. Đếm ngày trong một khoảng thời gian cụ thể
Để đếm ngày trong một khoảng thời hạn cụ thể, chúng ta có thể sử dụng công thức COUNTIFS cùng với hai tiêu chí hoặc 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 vào ngày hiện tại
Bạn có thể sử dụng hàm TODAY kết hợp với COUNTIF nhằm tính những ngày dựa trên ngày hôm nay.
Ví dụ: bí quyết COUNTIF sau với nhì vùng dữ 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ể chứa được nhiều biến. Ví dụ: chúng ta cũng có thể tinh chỉnh nó để đếm có bao nhiêu sản phẩm được cài đặt 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, giasuviet.edu.vn.hocexecl.online đã share cũng như khuyên bảo cho chúng ta một cách thực hiện công thức CountIFS cùng CountIF vào Excel một cách chi tiết nhất, giả dụ có bất kỳ thắc mắc nào chúng ta hãy phản hồi ngay ở bên dưới nội dung bài viết để công ty chúng tôi có thể xử lý mọi thắc mắc của công ty một giải pháp sớm nhất.