Trong 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 trên thuật logic 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ông giống nhau – số, ngày tháng, text cùng ô chứa dữ liệu.
Bạn đang xem: Cách sử dụng hàm countifs
Hàm COUNTIFS – bí quyết và bí quyết 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 trước 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 chí này khẳng định những ô tính nào sẽ được đếm và rất có thể biểu diễn là 10, “
Trong thực tế, bạn không hẳn thuộc lòng cú pháp của hàm COUNTIFS. Microsoft Excel đã hiển thị các đối số của hàm ngay khi bạn bước đầu gõ. Đối số được nhập vào hiện nay tại sẽ được tô đậm.
COUNTIFS – phần đa điều cần lưu giữ ý:
Bạn hoàn toàn có thể sử dụng hàm COUNTIFS để đếm các ô trong một phạm vi cá biệt với một đk duy nhất tương tự như trong các vùng dữ liệu với khá nhiều điều kiện. Trong trường hợp lắp thêm hai, chỉ phần đông ô 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 phải bao gồm cùng số hàng cùng cột như vùng dữ liệu thứ nhất (đối số criteria_range1).Có thể đếm hầu hết vùng tài liệu liền kề và ko liền 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ử lý nó như một giá chỉ trị bằng không (0).Bạn rất có thể sử dụng các wildcard characters trong các tiêu chí – dấu hoa thị (*) và dấu chấm hỏi (?).Cách sử dụng hàm COUNTIFS với COUNTIF vào excel với rất 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ông thức 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 có nhu cầu biết số mặt hàng tồn kho (giá trị cột B to hơn 0) nhưng lại chưa bán được (giá trị cột C bằng 0).
Nhiệm vụ hoàn toàn 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 được là 2 (“Cherries” với “Lemons”):
Công thức 2. Công thức 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 cung cấp mỗi cặp criteria_range / criteria một biện pháp riêng lẻ.
Ví dụ: đấy là công thức đúng để đếm các mục đựng 0 cả trong 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 vì chỉ gồm “Grapes” có mức giá trị “0” trong cả hai cột.
Sử dụng một công thức đơn giản và dễ dàng hơn với cùng 1 criteria_range duy tuyệt nhất như COUNTIFS(B2: C7, “= 0”) sẽ có lại tác dụng khác – tổng số các ô tính trong vòng B2:C7 chứa 0 (trong lấy một ví dụ này là 4).
Cách đếm số ô tính cùng với nhiều tiêu chí (OR logic):
Như chúng ta đã thấy trong ví dụ 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ế để vận động theo biện pháp này.
Nhưng nếu bạn muốn đếm các ô tất cả ít nhất 1 trong các tiêu chí được hướng dẫn và chỉ định là TRUE, tức thị dựa trên lô ghích OR thì sao? nhìn chung, gồm hai phương pháp để thực điều này – bằng cách thêm một số 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 nhì hoặc nhiều phương pháp COUNTIF hoặc COUNITFS
Trong bảng mặt dưới, trả sử bạn muốn tính các đơn đặt đơn hàng với tâm lý “Cancelled” và “Pending“, bạn bạn chỉ rất có thể viết 2 cách làm 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 đựng được nhiều hơn một điều kiện, thực hiện COUNTIFS thay bởi 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. Bí quyết SUM COUNTIFS với hằng mảng
Trong tình huống bạn cần thiết ập các tiêu chí, bí quyết tiếp cận trên chưa phải là cách tối ưu bởi phương pháp của các bạn sẽ quá dài dòng. Để tiến hành các phép tính tương tự trong một công thức nhỏ dại gọn hơn, hãy liệt kê toàn bộ các tiêu chí của khách hàng trong mảng và cung ứng mảng đó mang đến criteria của hàm COUNTIFS. Để đếm tổng, phối kết hợp hàm COUNTIFS bên trong hàm SUM như sau:
=SUM (COUNTIFS (range, “criteria1”, “criteria2”, “criteria3”, …))
Trong bảng chủng loại của chúng tôi, nhằm đếm các đơn đặt hàng có trạng thái “Cancelled“, “Pending” hoặc “In transit“, công thức sẽ như sau:
=SUM(COUNTIFS($C$2:$C$11,“Cancelled”,”Pending”,”In Transit”))
Tương tự, bạn cũng 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 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 khoảng số
Để tra cứu ra tất cả bao nhiêu số thân 5 cùng 10 (không bao hàm 5 và 10) được chứa trong số ô 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 bao 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: Xem Phim Tân Tiếu Ngạo Giang Hồ Tập 39 Vietsub, Xem Phim Tân Tiếu Ngạo Giang Hồ
=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – cách làm đếm gồm bao nhiêu số trong khúc từ 5 mang lại 10 ở trong vùng dữ iệu C2:C10, bao hàm cả 5 với 10.
Cách sử dụng tham chiếu ô trong công thức COUNTIFS
Khi sử dụng các toán tử xúc tích và ngắn gọn như “>”, “=” thuộc với những tham chiếu ô trong cách làm COUNTIFS, xem xét hãy đặt những toán tử trong ngoặc kép và thêm dấu (&) trước tham chiếu ô để xây dựng 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” cùng với số tiền to hơn $200. Với criteria_range1 trong A2: A11 cùng 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, chúng ta có thể nhập những giá trị tiêu chí và các ô tốt nhất định, ví dụ F1 cùng F2, tiếp nối tham chiếu phần đông ô kia trong bí quyết 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 để công thức vẫn đúng khi xào nấu sang các ô tính khác.
Cách áp dụng COUNTIFS với các ký từ đại diện:
Trong bí quyết Excel, bạn 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ự 1-1 nào, áp dụng nó để đếm các ô bắt đầu hoặc ngừng bằng một số trong những ký tự tố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 ô đựng một từ cụ thể hoặc một k1 tự vào ô.
Lưu ý. Nếu bạn có nhu cầu đếm các ô đựng dấu hỏi hoặc che dấu hoa thị, gõ dấu ngã (~) trước dấu hoa thị hoặc che dấu chấm hỏi.
Giả sử chúng ta có danh sách những dự án vào cột A. Bạn có nhu cầu 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ể sử dụng ký tự thay mặt đại diện trong tiêu chí thứ 2 do cột D đựng kiểu dữ liệu ngày tháng chứ chưa 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 ô 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 gian cụ thể
Để đếm ngày trong một khoảng thời gian cụ thể, bạn cũng có thể sử dụng công thức 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 tương đối 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 phù hợp với COUNTIF để tính các ngày dựa vào ngày hôm nay.
Ví dụ: công thức COUNTIF sau với hai vùng tài 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 chưa 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ó thể tinh chỉnh nó nhằm đếm có 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, quartetpress.com.hocexecl.online đã share cũng như giải đáp cho các bạn một cách thực hiện công thức CountIFS và CountIF vào Excel một cách chi tiết nhất, nếu có bất kỳ thắc mắc nào các bạn hãy bình luận ngay ở mặt dưới bài viết để shop 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.
