Trong vượt trình thao tác làm việc trên Excel, chúng ta phải làm quen với tương đối nhiều các hàm toán không giống nhau. Gồm có hàm chúng ta ít khi áp dụng đến, tuy vậy lại có những hàm chúng ta phải sử dụng thường xuyên, điển trong khi hàm COUNTIFS. Sẽ có rất nhiều bạn còn khá lạ với hàm này, mặc dù thế nó thật sự rất hữu ích để bạn cũng có thể sử dụng trong những phép toán đếm chứa đựng nhiều điều kiện. Hãy cùng Tech12h mày mò về hàm COUNTIFS.
Ở các bài trước, Tech12h đã giải đáp cho chúng ta một số hàm thực hiện trong Excel như hàm SUM để tính tổng, hàm SUMIF nhằm tính tổng gồm điều kiện, hàm đk IF giỏi hàm tính quý giá trung bình AVERAGE…và một trong những hàm khác nữa. Cùng hôm nay, Tech12 vẫn tiếp tục đưa tới cho chúng ta thêm một hàm mới nữa đấy là hàm COUNTIFS – hàm đếm chứa nhiều điều kiện.
Bạn đang xem: Hàm đếm có 2 điều kiện
Nội dung nội dung bài viết gồm 2 phần:
Giới thiệu về hàm COUNTIFSVí dụ minh họa hàm COUNTIFS1. Giới thiệu về hàm COUNTIFS
Hàm COUNTIFS là hàm dùng để làm đếm số ô vừa lòng hay xuất xắc nhiều điều kiện khác nhau.
Thực chất hàm COUNTIFS là hàm nâng cao của hàm COUNTIF. Theo đó, nó tất cả thẻ hạn chế và khắc phục được một trong những hạn chế cơ phiên bản mà hàm COUNTIF không khắc phục được. Ví dụ là nó rất có thể thực hiện nay phép đếm đựng nhiều điều kiện khác nhau một cách nhanh lẹ thay do hàm COUNTIF chỉ đếm được phép toán đựng một điều kiện.
Cú pháp của hàm như sau:
=COUNTIFS(Criteria_range1,Criteria1,
Trong đó:
Criteria_range1: vùng đếm 1 (bắt buộc)Criteria1: Điều khiếu nại đếm 1 (bắt buộc)Criteria_range2: Vùng đếm 2Criteria2: Điều khiếu nại đếm 2…..Ví dụ: Ta có bảng như sau:
Yêu cầu chúng ta đếm số nhân viên có ngày công là 23 ngày.
Ta tất cả công thức như sau:
=COUNTIFS(D2:D6,”nhân viên”,E2:E6,23) hiệu quả nhận được là 2
Để làm rõ hơn, ráng tiết hơn, bạn hãy đến với ví dụ bên dưới đây.
2. Ví dụ như minh họa hàm COUNTIFS
Ta bao gồm bảng tài liệu như sau:
Yêu cầu:
Đếm số nữ giới có 23 ngày công hồi tháng này?Đếm số nam bao gồm ngày nghỉ ngơi 0 ngày?Đếm số nữ giới có 23 ngày công với ngày nghỉ thấp hơn hoặc bởi 1 ngày?Để giải quyết và xử lý các phép toán trên, ta tiến hành triển khai như sau:
Thứ nhất, đếm số chúng ta nữa gồm 23 ngày công vào tháng này.
Đầu tiên, bạn phải xác định được các vùng đếm cùng điều kiện:
Vùng đếm 1: Cột giới tính C2:C9Điều kiện 1: NữVùng đếm 2: Cột ngày công E2:E9Điều khiếu nại 2: 23 ngày công
Từ so sánh như trên, gắn vào cú pháp ta được:
=COUNTIFS(C2:C9,"nữ",E2:E9,23) tác dụng thu được là 4
Thứ hai, đếm số nam gồm ngày nghỉ 0 ngày.
Cũng tương tự, ta khẳng định vùng đếm cùng điều kiện:
Vùng đếm 1: Cột nam nữ C2:C9Điều khiếu nại 1: NamVùng đếm 2: Cột ngày nghỉ ngơi F2:F9Điều kiện 2: 0 ngày nghỉ
Từ kia ta bao gồm công thức tính như sau:
=COUNTIFS(C2:C9,"nam",F2:F9,0) hiệu quả thu được là 0.
Cuối cùng, đếm số bạn nữ có 23 ngày công và bao gồm ngày ngủ thấp hơn hoặc bởi 1 ngày.
Khác với hai thắc mắc ở trên, ở thắc mắc này tất cả chứa ba điều kiện.
Vùng đếm 1: Cột giới tính C2:C9Điều khiếu nại 1: NữVùng đếm 2: Cột ngày công E2:E9Điều khiếu nại 2: 23 ngày
Vùng đếm 3: Cột ngày nghỉ F2:F9Điều khiếu nại 3: 0 ngày
Chỉ đề xuất lắp vào cú pháp ta được phương pháp tính như sau:
=COUNTIFS(C2:C9,"nữ",E2:E9,23,F2:F9,0) tác dụng thu được là 2.
Như vậy, tôi đã hướng dẫn cho các bạn làm quen và áp dụng hàm COUNTIFS. Nó sẽ không quá khó nếu như bạn nắm bắt và phát âm được bí quyết tính của nó. Nếu như vẫn chưa tiến hành được, các bạn có thể comment dưới để được phía dẫn cụ thể hơn. Ngoài ra, để tìm hiểu thêm các bạn có thể bài viết liên quan bài viết những hàm cơ bản trên Excel. Cứng cáp chắn, nó vẫn cung cấp cho chính mình rất những điều tuyệt về nguyên lý này. Ví như cảm thấy bài viết bổ ích, các bạn hãy lượt thích và Share bỏ nhiều người được nghe biết hơn nhé. Xin cảm ơn!
Trong bài viết này, các các bạn sẽ được chỉ dẫn cách sử dụng hàm đếm số lượng COUNTIF cùng COUNIFS dựa vào thuật logic AND và OR. Các bạn sẽ tìm thấy không hề ít ví dụ với những kiểu dữ liệu khác nhau – số, ngày tháng, text và ô cất 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ẽ được đếm và rất có thể biểu diễn là 10, “Bạn có thể sử dụng hàm COUNTIFS để đếm những ô trong một phạm vi riêng biệt với một điều kiện duy nhất cũng tương tự trong những vùng dữ liệu với khá nhiều điều kiện. Vào trường hợp thứ hai, chỉ đầy đủ ô 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 phải gồm cùng số hàng và cột như vùng dữ liệu đầu tiên (đối số Criteria_range1).Có thể đếm phần nhiều vùng tài liệu liền kề và không tức tốc kề.Nếu tiêu chuẩn là một tham chiếu mang đến một ô trống, hàm COUNTIFS giải pháp xử lý nó như một giá bán trị bằng không.Bạn hoàn toàn có thể sử dụng các Wildcard characters vào các tiêu chí – lố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 cùng với các tiêu chuẩn khác nhau
Giả sử chúng ta có một danh sách sản phẩm như trong hình mặt dưới. Bạn muốn biết số sản phẩm 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ụ có thể được thực hiện bằng cách sử dụng phương pháp 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 đơ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ẽ sở hữu lại tác dụng khác – tổng số những ô tính trong vòng B2:C7 đựng 0 (trong lấy một ví dụ này là 4).
Công thức 2. Công thức COUNTIFS cùng 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 chuẩn giống nhau, chúng ta vẫn phải cung cấp mỗi cặp criteria_range / criteria một phương pháp riêng lẻ.
Ví dụ: đây là công thức đúng nhằm đếm các mục chứa 0 cả vào 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ỉ gồm “Grapes” 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ư các bạn đã thấy trong lấy ví dụ như trên, việc đếm những ô 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 bởi vì hàm COUNTIFS được thiết kế theo phong cách để hoạt động theo giải 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 hướng dẫn và chỉ định là TRUE, nghĩa là dựa trên xúc tích và ngắn gọn OR thì sao? chú ý chung, tất cả hai cách để thực điều này – bằng phương pháp thêm một số 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 hai hoặc nhiều công thức COUNTIF hoặc COUNITFS
Trong bảng mặt dưới, giả sử bạn có nhu cầu tính các đơn đặt hàng với trạng thái “Cancelled” và “Pending“, bạn chỉ rất có thể viết 2 phương pháp 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ụ: để đếm số đối chọi đặ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 các ô trong khoảng số
Để tra cứu ra tất cả bao nhiêu số thân 5 và 10 (không bao gồm 5 với 10) được chứa trong những ô C2 cho 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 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.
=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – bí quyết đếm có bao nhiêu số trong đoạn từ 5 đến 10 trực thuộc vùng dữ iệu C2:C10, bao hàm cả 5 và 10.
Cách sử dụng tham chiếu ô trong phương pháp COUNTIFS
Khi sử dụng những toán tử ngắn gọn xúc tích như “>”, “=” thuộc với các tham chiếu ô trong công thức COUNTIFS, lưu ý hãy đặt các toán tử vào ngoặc kép và 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 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ũ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 các giá trị tiêu chuẩn và các ô duy nhất định, lấy một ví dụ F1 với F2, kế tiếp tham chiếu đầy đủ ô 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 coppy sang các ô 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ự đối kháng nào, áp dụng nó để đếm các ô bắt đầu hoặc xong bằng một vài ký tự tuyệ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ó nhằm đếm ô chứa một từ ví dụ 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 dấu hoa thị, gõ dấu xẻ (~) trước vết hoa thị hoặc che 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 có nhu cầu biết bao gồm bao nhiêu dự án đã 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 điều kiện thứ nhị – End date trong cột D.
=COUNTIFS(B2:B10,”*”,D2:D10,””&””))
Lưu ý, bạn không thể áp 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ứ chưa hẳn dạng text. Đó là tại sao tại sao bạn thực hiện 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ể, 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: “Quét Sạch” 7 Nhà Hàng Người Hoa Ở Sài Gòn, Nổi Tiếng, Nhất Định Phải Thử Một Lần!
Ví dụ 3. Đếm ngày với khá nhiều điều kiện dựa trên ngày hiện nay tại
Bạn rất có thể sử dụng hàm =TODAY() kết vừa lòng với COUNTIF để tính những 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 mình biết có bao nhiêu sản phẩm đã được thiết lập nhưng không giao hàng: