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