hàm countifs nhiều điều kiện

Trong nội dung bài viết này, Học Excel Online tiếp tục chỉ dẫn cơ hội dùng công thức Hàm COUNTIFS và dùng Hàm COUNTIF vô Excel dựa bên trên thuật logic AND và OR. Quý khách hàng tiếp tục nhìn thấy thật nhiều ví dụ với những loại tài liệu không giống nhau – số, tháng ngày, text và dù chứa chấp tài liệu.

Xem thêm:

Bạn đang xem: hàm countifs nhiều điều kiện

• Hàm Count vô Excel
• Hàm CountA vô Excel
• Đánh số tự động hóa vì chưng hàm CountA
• Tự học tập thạo Excel 

Hàm COUNTIFS – công thức và cơ hội sử dụng

Công thức hàm COUNTIFS như sau:

=COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2]…)

criteria_range1 – xác lập phạm vi thứ nhất vận dụng ĐK thứ nhất (criteria1), đề nghị.

criteria1 – bịa đặt ĐK mang lại 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, đề nghị. Tiêu chí này xác lập những dù tính này sẽ tiến hành kiểm đếm và rất có thể màn trình diễn là 10, “<=32”, A6, “sweets”.

[criteria_range2, criteria2]… – đấy là những ĐK bổ sung cập nhật, tùy lựa chọn.  Quý khách hàng rất có thể nhập cho tới 127 cặp ĐK vô công thức.

Trong thực tiễn, các bạn ko cần nằm trong lòng cú pháp của hàm COUNTIFS. Microsoft Excel tiếp tục hiển thị những đối số của hàm tức thì khi chúng ta chính thức gõ. Đối số được nhập vô lúc này sẽ tiến hành tô đậm.

COUNTIFS – Những điều cần lưu ý:

1. Bạn rất có thể dùng hàm COUNTIFS nhằm kiểm đếm những dù vô một phạm vi đơn lẻ với 1 ĐK độc nhất hao hao trong vô số vùng tài liệu với tương đối nhiều ĐK. Trong tình huống loại nhị, chỉ những dù tính đáp ứng toàn bộ những điều kiện vừa mới được kiểm đếm.
2. Mỗi vùng tài liệu bổ sung cập nhật cần đem cùng số sản phẩm và cột như vùng tài liệu thứ nhất (đối số criteria_range1).
3. Có thể kiểm đếm những vùng tài liệu liền kề và ko ngay lập tức kề.
4. Nếu tiêu chuẩn là một trong những tham ô chiếu cho tới một dù trống, hàm COUNTIFS xử lý nó như 1 độ quý hiếm vì chưng ko (0).
5. Bạn rất có thể dùng những wildcard characters trong những tiêu chuẩn – vết hoa thị (*) và vết chấm chất vấn (?).

Cách dùng hàm COUNTIFS và COUNTIF vô excel với tương đối nhiều chi tiêu chí:

Cách kiểm đếm dù tính với tương đối nhiều tiêu chuẩn (AND logic):

Công thức 1. Công thức COUNTIFS với những tiêu chuẩn không giống nhau

Giả sử các bạn mang trong mình 1 list thành phầm như vô hình bên dưới. Quý khách hàng ham muốn biết số sản phẩm tồn kho (giá trị cột B to hơn 0) tuy nhiên ko bán tốt (giá trị cột C vì chưng 0).

Nhiệm vụ rất có thể được triển khai bằng phương pháp dùng công thức này:

= COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”)

Kết ngược cảm nhận được là 2 (“Cherries” và “Lemons”):

Công thức 2. Công thức COUNTIFS với những tiêu chuẩn giống như nhau

Khi mình thích kiểm đếm những mục đem những tiêu chuẩn giống như nhau, các bạn vẫn cần hỗ trợ từng cặp criteria_range / criteria một cơ hội riêng biệt lẻ.

Ví dụ: đấy là công thức đích nhằm kiểm đếm những mục chứa chấp 0 cả vô 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ì thế chỉ mất “Grapes” có mức giá trị “0” vô cả nhị cột.

Sử dụng một công thức giản dị rộng lớn với 1 criteria_range duy nhất như COUNTIFS(B2: C7, “= 0”) tiếp tục đưa đến thành phẩm không giống – tổng số những dù tính trong tầm B2:C7 chứa chấp 0 (trong ví dụ này là 4).

Xem thêm: Tổng ăn ý kỹ năng về Excel nâng cao

Cách kiểm đếm số dù tính với tương đối nhiều tiêu chuẩn (OR logic):

Như các bạn vẫn thấy vô ví dụ bên trên, việc kiểm đếm những dù tính thỏa mãn nhu cầu toàn bộ những tiêu chuẩn hướng đẫn vô cùng đơn giản chính vì hàm COUNTIFS được kiến thiết nhằm sinh hoạt Theo phong cách này.

Nhưng nếu như mình thích kiểm đếm những dù đem ít nhất một trong những tiêu chuẩn được hướng đẫn là TRUE, tức là dựa vào logic OR thì sao? Nhìn công cộng, đem nhị phương pháp để thực điều này – bằng phương pháp tăng một vài công thức COUNTIF hoặc 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 công thức COUNTIF hoặc COUNITFS

Trong bảng bên dưới, fake sử mình thích tính những đơn đặt mua với tình trạng “Cancelled” và “Pending“, các bạn các bạn chỉ rất có thể ghi chép 2 công thức COUNTIF thông thường và nhận kết quả:

=COUNTIF($C$2:$C$11,”Cancelled”)+COUNTIF($C$2:$C$11,”Pending”)

Trong tình huống từng hàm chứa chấp nhiều hơn thế nữa một ĐK, dùng COUNTIFS chứ không COUNTIF. Ví dụ: nhằm kiểm đếm số đơn đặt mua “Cancelled”  và “Pending” đối với “Apples”, 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. Công thức SUM COUNTIFS với hằng mảng

Trong trường hợp các bạn quan trọng ập nhiều tiêu chuẩn, cơ hội tiếp cận bên trên ko cần là cơ hội tối ưu vì chưng công thức của các bạn sẽ quá dông dài. Để triển khai những phép tắc tính tương tự động vô một công thức không lịch kịch rộng lớn, hãy liệt kê toàn bộ những tiêu chuẩn của người sử dụng vô mảng và hỗ trợ mảng cơ mang lại criteria của hàm COUNTIFS. Để kiểm đếm tổng, phối 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 kiểu mẫu của Shop chúng tôi, nhằm kiểm đếm những đơn đặt mua đem tình trạng “Cancelled“, “Pending” hoặc “In transit“, công thức tiếp tục như sau:

=SUM(COUNTIFS($C$2:$C$11,{“Cancelled”,”Pending”,”In Transit”}))

Tương tự động, chúng ta cũng có thể kiểm đếm những dù dựa vào nhị hoặc nhiều hơn thế nữa những cặp criteria_range/criteria. Ví dụ: để tìm hiểu con số đơn đặt mua “Apples” vô tình trạng “Cancelled”, “Pending”, “In transit”, 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 kiểm đếm số trong tầm cụ thể:

Công thức 1. Hàm COUNTIFS kiểm đếm những dù trong tầm số

Để lần đi ra đem từng nào số thân thuộc 5 và 10 (không bao hàm 5 và 10) được chứa chấp trong những dù C2 cho tới C10, dùng công thức này:

=COUNTIFS(C2:C10,”>5″,C2:C10,”<10″)

Xem thêm: thế nào là khai báo biến mảng gián tiếp

Để bao hàm cả 5 và 10, dùng “lớn rộng lớn hoặc bằng” và “nhỏ rộng lớn hoặc bằng”:

=COUNTIFS(B2:B10,”>=5″,B2:B10,”<=10″)

Công thức 2. Công thức COUNTIF nhằm kiểm đếm số thân thuộc X và Y

Chúng tớ rất có thể dùng công thức COUNTIF lồng ghép với 1 hàm không giống khác để sở hữu thành phẩm tương tự động. Công thức thứ nhất kiểm đếm những số to hơn độ quý hiếm số lượng giới hạn bên dưới (ví dụ là 5). Công thức loại nhị vô hiệu những số to hơn độ quý hiếm số lượng giới hạn bên trên (trong tình huống này là 10). Kết ngược mình thích lần ở trong tầm nhị độ quý hiếm cảm nhận được bên trên.

=COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”=10″) – kiểm đếm đem từng nào số to hơn 5 nhỏ rộng lớn 10 vô vùng tài liệu C2:C10.

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – công thức kiểm đếm đem từng nào số trong khúc kể từ 5 cho tới 10 nằm trong vùng dữ iệu C2:C10, bao hàm cả 5 và 10.

Cách dùng tham ô chiếu dù vô công thức COUNTIFS

Khi dùng những toán tử logic như “>”, “<“, “<=” hoặc “>=” cùng theo với những tham ô chiếu dù vô công thức COUNTIFS, cảnh báo hãy bịa đặt những toán tử vô ngoặc kép và tăng vết (&) trước tham ô chiếu dù nhằm kiến tạo một chuỗi văn bạn dạng.

Trong tập dượt tài liệu kiểu mẫu tiếp sau đây, hãy tính những lô hàng “Apples” với số chi phí to hơn $200. Với criteria_range1 vô A2: A11 và criteria_range2 vô B2: B11, chúng ta cũng có thể dùng công thức:

=COUNTIFS($A$2:$A$11,”Táo”,$B$2:$B$11,”>200″)

Hoặc, chúng ta cũng có thể nhập những độ quý hiếm tiêu chuẩn và những dù chắc chắn, ví dụ F1 và F2, tiếp sau đó tham ô chiếu những dù cơ vô công thức của bạn:

=COUNTIFS($A$2:$A$11,$F$1,$B$2:$B$11,”>&$F$2)

Chú ý dùng độ quý hiếm tuyệt đối trong criteria và criteria_range để công thức vẫn đúng vào khi sao chép quý phái những dù tính không giống.

Xem thêm: Khoá học tập Excel kể từ cơ bạn dạng cho tới nâng cao

Cách dùng COUNTIFS với những ký tự động đại diện:

Trong công thức Excel, chúng ta cũng có thể dùng những ký tự động đại diện thay mặt sau đây:

Dấu chất vấn (?) – khớp với ngẫu nhiên ký tự động đơn này, dùng nó nhằm kiểm đếm những dù chính thức hoặc kết đốc vì chưng một vài ký tự động chắc chắn.

Dấu hoa thị (*) – khớp với ngẫu nhiên sản phẩm ký tự động này, các bạn người sử dụng nó nhằm kiểm đếm dù có một kể từ ví dụ hoặc một k1 tự động vô dù.

Lưu ý. Nếu mình thích kiểm đếm những dù chứa chấp vết chất vấn hoặc vết hoa thị, gõ vết té (~) trước vết hoa thị hoặc vết chấm chất vấn.

Giả sử các bạn đem list những dự án công trình vô cột A. Quý khách hàng ham muốn biết đem từng nào dự án công trình đã ký kết kết, tức là cột B đem chứa chấp thương hiệu người. Hãy thêm 1 ĐK loại nhị – End date trong cột D.

=COUNTIFS(B2:B10,”*”,D2:D10,”<>”&””))

Lưu ý, các bạn ko thể dùng ký tự động đại diện thay mặt vô tiêu chuẩn thứ hai vì thế cột D chứa chấp loại tài liệu tháng ngày chứ không cần cần dạng text. Đó là nguyên do tại vì sao các bạn dùng tiêu chuẩn “<>”&”” nhằm lần dù chứa chấp tài liệu.

COUNTIFS và COUNTIF với tương đối nhiều tiêu chuẩn ngày tháng:

Ví dụ 1. Đếm ngày vô một khoảng tầm thời hạn cụ thể

Để kiểm đếm ngày vô một khoảng tầm thời hạn ví dụ, chúng ta cũng có thể dùng công thức COUNTIFS với nhị tiêu chuẩn hoặc phối kết hợp nhị hàm COUNTIF cùng nhau.

Ví dụ, những công thức tại đây kiểm đếm số ngày trong những dù C2 cho tới C10, khoảng tầm thời hạn từ thời điểm ngày 1/6/2014 cho tới 7/7/2014 bao gồm:

=COUNTIFS(C2:C9,”=6/1/2014″,C2:C9,”<=6/7/2014″)

=COUNTIF(C2:C9,”=6/1/2014″)-COUNTIF(C2:C9,”6/7/2014″)

Ví dụ 2. Đếm ngày với tương đối nhiều điều kiện

Tương tự động, chúng ta cũng có thể dùng công thức COUNTIFS nhằm kiểm đếm số ngày trong những cột không giống nhau thỏa mãn nhu cầu kể từ nhị ĐK trở lên trên. Ví dụ, công thức tiếp sau đây tiếp tục lần đi ra đem từng nào thành phầm được mua sắm sau ngày trăng tròn mon 5 và ship hàng sau ngày thứ nhất mon 6:

=COUNTIFS(C2:C9,”>5/1/2014″,D2:D9,”6/7/2014″)

Ví dụ 3. Đếm ngày với tương đối nhiều ĐK dựa vào ngày hiện tại tại

Bạn rất có thể dùng hàm TODAY kết phù hợp với COUNTIF nhằm tính những ngày dựa vào ngày thời điểm ngày hôm nay.

Ví dụ: công thức COUNTIF sau với nhị vùng tài liệu và nhị tiêu chuẩn tiếp tục cho chính mình biết đem từng nào thành phầm đã và đang được mua sắm tuy nhiên ko giao phó hàng:

=COUNTIFS(C2:C9,”<“&TODAY(),D2:D9,”>”&TODAY())

Công thức này rất có thể chứa đựng nhiều đổi mới. Ví dụ: chúng ta cũng có thể tinh ranh chỉnh nó nhằm kiểm đếm đem từng nào thành phầm được mua sắm rộng lớn một tuần trước đó và ko giao phó hàng:

Xem thêm: công dân thực hiện quyền sáng tạo trong trường hợp nào sau đây

=COUNTIFS(C2:C9,”<=”&TODAY()-7,D2:D9,”>”&TODAY())

Như vậy với nội dung bài viết này, blog.hocexecl.online vẫn share hao hao chỉ dẫn mang lại chúng ta một cơ hội dùng công thức CountIFS và CountIF vô Excel một cơ hội cụ thể nhất, nếu như đem ngẫu nhiên vướng mắc này chúng ta hãy comment tức thì ở bên dưới nội dung bài viết nhằm Shop chúng tôi rất có thể xử lý từng vướng mắc của người sử dụng một cơ hội nhanh nhất.

Những kỹ năng các bạn đang được coi nằm trong khóa học Excel kể từ cơ bạn dạng cho tới nâng lên của Học Excel Online. Khóa học tập này hỗ trợ cho chính mình kỹ năng một cơ hội rất đầy đủ và đem khối hệ thống về những hàm, những dụng cụ vô excel, phần mềm excel vô công việc… Hiện ni khối hệ thống đang sẵn có nhiều ưu đãi khi chúng ta ĐK nhập cuộc khóa đào tạo này. Hãy nhập cuộc tức thì bên trên địa chỉ: Học Excel Online