Trong nội dung bài viết này, Học Excel Online sẽ gợi ý cách thực hiện công thức Hàm COUNTIFS và áp dụng Hàm COUNTIF trong Excel dựa bên trên thuật logic AND và OR. Các bạn sẽ tìm thấy tương đối nhiều ví dụ với những kiểu dữ liệu không giống nhau – số, ngày tháng, text và ô chứa dữ liệu.
Bạn đang xem: Hàm đếm có nhiều điều kiện
Hàm COUNTIFS – công thức 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 đầu tiên (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 không phải thuộc lòng cú pháp của hàm COUNTIFS. Microsoft Excel vẫn hiển thị những đối số của hàm ngay 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 – những điều cần lưu lại ý:
Bạn rất có thể sử dụng hàm COUNTIFS nhằm đếm những ô vào một phạm vi lẻ tẻ với một đk duy nhất cũng giống như trong các vùng dữ liệu với rất nhiều điều kiện. Trong trường hợp vật dụng hai, chỉ hầu hết ô tính đáp ứng tất cả các điều kiện mới được đếm.Mỗi vùng dữ liệu bổ sung cập nhật phải gồm cùng số hàng và cột như vùng dữ liệu thứ nhất (đối số criteria_range1).Có thể đếm hồ hết vùng tài liệu liền kề và ko liền kề.Nếu tiêu chí là một tham chiếu cho một ô trống, hàm COUNTIFS xử trí nó như một giá bán trị bằng không (0).Bạn có thể sử dụng các 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 trong excel vớ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. Bí quyết COUNTIFS với các tiêu chí khác nhau
Giả sử bạn có một danh sách sản phẩm như vào hình bên dưới. Bạn có nhu cầu biết số mặt hàng tồn kho (giá trị cột B lớn hơn 0) mà lại chưa bán tốt (giá trị cột C bằng 0).
Nhiệm vụ hoàn toàn 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ông thức COUNTIFS với các tiêu chí giống nhau
Khi bạn muốn đếm các 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 bí quyết riêng lẻ.
Ví dụ: đó là công thức đúng nhằm đếm các mục cất 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 vì chỉ tất cả “Grapes” có mức giá trị “0” trong cả nhì 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 tốt nhất như COUNTIFS(B2: C7, “= 0”) sẽ sở hữu lại tác dụng khác – tổng số những ô tính trong tầm B2:C7 chứa 0 (trong lấy 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ụ như trên, việc đế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 dàng cũng chính vì hàm COUNTIFS có thiết kế để vận động theo biện pháp này.
Nhưng nếu bạn có nhu cầu đếm những ô gồm ít nhất 1 trong những các tiêu chí được chỉ định là TRUE, tức thị 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 này – bằng cách thêm một 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, trả sử bạn muốn tính các đơn đặt đơn hàng với tinh thần “Cancelled” và “Pending“, các bạn bạn chỉ rất có thể viết 2 bí quyết 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 nhiều hơn một điều kiện, sử dụng COUNTIFS thay vì chưng COUNTIF. Ví dụ: nhằm đếm số đơn đặt đơn hàng “Cancelled” và “Pending” đối với “Apples”, sử 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. Bí quyết SUM COUNTIFS cùng với hằng mảng
Trong tình huống bạn quan trọng ập những tiêu chí, giải pháp tiếp cận trên không phải là bí quyết tối ưu bởi phương pháp của các 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 bé 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à hỗ trợ mảng đó đến criteria của hàm COUNTIFS. Để đếm tổng, phối 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 các đơn mua hàng có trạng thái “Cancelled“, “Pending” hoặc “In transit“, cách làm sẽ như sau:
=SUM(COUNTIFS($C$2:$C$11,“Cancelled”,”Pending”,”In Transit”))
Tương tự, chúng ta cũng có thể đếm những ô dựa vào hai hoặc nhiều hơn nữa các cặp criteria_range/criteria. Ví dụ: để biết con số đơn đặt đơn hàng “Apples” trong tâm trạng “Cancelled”, “Pending”, “In transit”, sử dụng 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 tầm số
Để tra cứu ra gồm bao nhiêu số giữa 5 và 10 (không bao gồm 5 với 10) được chứa trong số ô C2 mang đế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 tất cả bao nhiêu số to hơn 5 nhỏ tuổi hơn 10 vào vùng dữ liệu C2:C10.
=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – bí quyết đếm bao gồm bao nhiêu số trong khúc từ 5 đến 10 thuộc vùng dữ iệu C2:C10, bao hà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ử logic như “>”, “=” thuộc với các tham chiếu ô trong cách làm COUNTIFS, để ý hãy đặt các toán tử vào ngoặc kép cùng thêm lốt (&) trước tham chiếu ô để xuất bản một chuỗi văn bản.
Trong tập dữ liệu mẫu dưới đây, hãy tính các đơn hàng “Apples” với số tiền lớn hơn $200. Cùng với criteria_range1 vào A2: A11 với 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, bạn có thể nhập các giá trị tiêu chuẩn và số đông ô nhất định, ví dụ như F1 với F2, tiếp nối tham chiếu gần như ô đó trong phương pháp 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 xào nấu sang những ô tính khác.
Cách sử dụng COUNTIFS với những ký tự đại diện:
Trong công thức Excel, bạn cũng có thể sử dụng những ký tự đại diện sau đây:
Dấu hỏi (?) – khớp với ngẫu nhiên ký tự đối chọi nào, áp dụng nó nhằm đếm những ô 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 ngẫu nhiên dãy cam 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ự trong ô.
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 xẻ (~) trước lốt hoa thị hoặc giấu chấm hỏi.
Giả sử chúng ta có danh sách những dự án trong cột A. Bạn muốn biết có bao nhiêu dự án công trình đã ký kết, có nghĩa là cột B tất cả chứa thương hiệu người. Hãy thêm một đk 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ự đại diện thay mặt trong tiêu chí thứ 2 vì chưng cột D chứa kiểu tài liệu ngày tháng chứ không phải dạng text. Đó là nguyên nhân tại sao các bạn sử dụng tiêu chuẩn “”&”” để tìm ô chứa 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ó thể sử dụng bí quyết COUNTIFS cùng với hai tiêu chí hoặc phối kết hợp hai hàm COUNTIF cùng với nhau.
Ví dụ 3. Đếm ngày với rất nhiều điều kiện dựa vào ngày hiện nay tại
Bạn hoàn toà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ụ: bí quyết COUNTIF sau với nhị vùng tài liệu và hai tiêu chuẩn sẽ cho bạn biết tất cả bao nhiêu sản phẩm đã được thiết lập nhưng không giao hàng:
=COUNTIFS(C2:C9,””&TODAY())
Công thức này còn có thể chứa đựng nhiều biến. Ví dụ: chúng ta có thể tinh chỉnh nó nhằm đếm gồm bao nhiêu sản phẩm được cài 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, blog.hocexecl.online đã chia sẻ cũng như khuyên bảo cho chúng ta một cách sử dụng công thức Count
IFS với Count
IF vào Excel một cách chi tiết nhất, trường hợp có ngẫu nhiên thắc mắc nào các bạn hãy bình luận ngay ở bên dưới nội dung bài viết để cửa hàng chúng tôi có thể giải quyết và xử lý mọi thắc mắc của người tiêu dùng một bí quyết sớm nhất.
Trong bài viết này, các các bạn sẽ được lý giải cách thực hiện hàm đếm số lượng COUNTIF với COUNIFS dựa trên thuật logic AND với OR. Bạn sẽ tìm thấy tương đối nhiều ví dụ với các kiểu dữ liệu không giống nhau – số, ngày tháng, text và ô chứa dữ liệu.
CÔNG THỨC HÀM COUNTIFS
=COUNTIFS (Criteria_range1, Criteria1,
Criteria_range1 – khẳng đị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 chí 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, “Bạn có thể sử dụng hàm COUNTIFS nhằm đếm các ô trong một phạm vi đơn lẻ với một đk duy nhất cũng tương tự trong các vùng dữ liệu với tương đối nhiều điều kiện. Vào trường hợp đồ vật hai, chỉ hồ hết ô tính đáp ứng nhu cầu tất cả các điều kiện bắt đầu được đếm.Mỗi vùng dữ liệu bổ sung cập nhật phải tất cả cùng số hàng với cột như vùng dữ liệu thứ nhất (đối số Criteria_range1).Có thể đếm gần như vùng tài liệu liền kề cùng không ngay lập tức kề.Nếu tiêu chuẩn là một tham chiếu đến một ô trống, hàm COUNTIFS xử trí nó như một giá chỉ trị bằng không.Bạn hoàn toàn có thể sử dụng các Wildcard characters trong các tiêu chuẩn – vết hoa thị (*) và 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ư vào hình bên 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 mà chưa bán được (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 cách làm này:
= COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”)
Kết quả nhận thấy là 2 (“Cherries” với “Lemons”)
Sử dụng một công thức dễ dàng và đơn giản hơn cùng với một criteria_range duy tốt 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 đựng 0 (trong lấy ví dụ này là 4).
Công thức 2. Cách làm 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 chí giống nhau, chúng ta vẫn phải cung ứng 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 nhằm đếm các mục đựng 0 cả trong 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 vì chưng chỉ gồm “Grapes” có mức giá trị “0” vào cả hai cột.
CÁCH ĐẾM SỐ Ô TÍNH VỚI NHIỀU TIÊU CHÍ (OR LOGIC)
Như các bạn đã thấy trong lấy ví dụ trên, việc đếm những ô tính thỏa mãn nhu cầu tất cả các tiêu chí chỉ định rất dễ dàng bởi vì hàm COUNTIFS được thiết kế để vận động theo phương pháp này.
Nhưng nếu bạn muốn đếm những ô có ít nhất một trong các tiêu chí được chỉ định và hướng dẫn là TRUE, tức thị dựa trên logic OR thì sao? chú ý chung, có hai cách để thực điều này – bằng phương pháp 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 cách làm COUNTIF hoặc COUNITFS
Trong bảng bên dưới, trả sử bạn có nhu cầu tính các đơn đặt hàng với tâm lý “Cancelled” cùng “Pending“, chúng ta chỉ hoàn toàn 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 phù 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ố solo đặt hàng “Cancelled” và “Pending” đối 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Á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 bao gồm bao nhiêu số giữa 5 với 10 (không bao hàm 5 cùng 10) được chứa trong số ô C2 mang đế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 có bao nhiêu số to hơn 5 nhỏ tuổi hơn 10 vào vùng dữ liệu C2:C10.
=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – công thức đếm gồm bao nhiêu số trong khúc từ 5 mang lại 10 thuộc vùng dữ iệu C2:C10, bao hàm cả 5 với 10.
Cách áp dụng tham chiếu ô trong bí quyết COUNTIFS
Khi sử dụng các toán tử súc tích như “>”, “=” thuộc với các tham chiếu ô vào công thức COUNTIFS, chú ý hãy đặt các toán tử vào ngoặc kép với thêm vệt (&) trước tham chiếu ô để thành lập một chuỗi văn bản.
Trong tập dữ liệu mẫu dưới đây, hãy tính các deals “Apples” với số tiền to hơn $200. Với criteria_range1 trong A2: A11 và 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, bạn cũng có thể nhập những giá trị tiêu chí và các ô tốt nhất định, lấy ví dụ như F1 và F2, tiếp nối tham chiếu đầy đủ ô đó trong công thức 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 những ô tính khác.
CÁCH SỬ DỤNG COUNTIFS VỚI CÁC KÝ TỰ ĐẠI DIỆN
Trong công thức Excel, chúng ta cũng có thể sử dụng những ký tự thay mặt sau đây:
Dấu hỏi (?) – khớp với ngẫu nhiên ký tự solo nào, sử dụng nó để đếm những ô ban đầu hoặc kết thúc bằng một số trong những ký tự độc 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 ô đựng một từ rõ ràng hoặc một k1 tự trong ô.
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ửa (~) trước lốt hoa thị hoặc giấu chấm hỏi.
Giả sử các bạn có danh sách những dự án trong cột A. Bạn muốn biết bao gồm bao nhiêu dự án đã ký kết kết, có nghĩa là cột B có chứa thương hiệu người. Hãy thêm một đk thứ nhì – End date trong cột D.
=COUNTIFS(B2:B10,”*”,D2:D10,””&””))
Lưu ý, chúng ta không thể sử dụng ký tự thay mặt trong tiêu chuẩn thứ 2 vì chưng cột D cất kiểu dữ liệu ngày tháng chứ chưa hẳn dạng text. Đó là nguyên nhân tại sao bạn sử dụng tiêu chí “”&”” để tìm kiế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 gian 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.
Xem thêm: Hướng Dẫn Cách Tăng Điểm Đấu Sĩ Mu Online Từ A, Cách Chơi Mg (Đấu Sĩ) Trong Game Mu Online
Ví dụ 3. Đếm ngày với khá nhiều điều kiện dựa trên ngày hiện tại tại
Bạn rất có thể sử dụng hàm =TODAY() kết hòa 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 nhì vùng dữ liệu và hai tiêu chí sẽ cho mình biết có bao nhiêu sản phẩm đã được tải nhưng không giao hàng: