Hàm countif và cách đếm có điều kiện trong excel

Trong bài viết này, Học Excel Online sẽ lí giải giải pháp thực hiện bí quyết Hàm COUNTIFS với thực hiện Hàm COUNTIF vào Excel dựa vào thuật súc tích AND OR. Quý Khách vẫn search thấy rất nhiều ví dụ cùng với những loại tài liệu không giống nhau – số, ngày tháng, text và ô cất dữ liệu.

Bạn đang xem: Hàm countif và cách đếm có điều kiện trong excel


Hàm COUNTIFS – cách làm với giải pháp sử dụng

Công thức hàm COUNTIFS nlỗi sau:

=COUNTIFS (criteria_range1, criteria1, …)

criteria_range1 – xác định phạm vi trước tiên vận dụng ĐK trước tiên (criteria1), bắt buộc.

criteria1 – đặt điều kiện mang lại dạng số, ô tsi mê chiếu, chuỗi văn uống bản, một mảng hoặc một hàm Excel khác, phải. Tiêu chí này xác minh phần nhiều ô tính nào sẽ được đếm với hoàn toàn có thể màn trình diễn là 10, “

*

*

Trong thực tiễn, bạn chưa hẳn nằm trong 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ập tức khi chúng ta bước đầu gõ. Đối số được nhtràn vào hiện tại sẽ được đánh đậm.

*

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

Quý khách hàng có thể thực hiện hàm COUNTIFS nhằm đếm các ô trong một phạm vi riêng lẻ với 1 ĐK tuyệt nhất cũng giống như trong không ít vùng tài liệu với nhiều ĐK. Trong ngôi trường vừa lòng thiết bị nhị, chỉ mọi ô tính thỏa mãn nhu cầu toàn bộ các điều kiện mới được đếm.Mỗi vùng dữ liệu bổ sung đề xuất tất cả thuộc số mặt hàng cùng cột nlỗi vùng dữ liệu trước tiên (đối số criteria_range1).cũng có thể đếm số đông vùng dữ liệu giáp ko liền kề.Nếu tiêu chí là một tsi chiếu mang đến một ô trống, hàm COUNTIFS xử trí nó nlỗi một quý hiếm bằng ko (0).Quý Khách rất có thể thực hiện các wildthẻ characters trong những tiêu chí – lốt hoa thị (*) với vết chấm hỏi (?).

Cách áp dụng hàm COUNTIFS và COUNTIF vào excel với nhiều tiêu chí:

Cách đếm ô tính với rất nhiều tiêu chuẩn (AND logic):

Công thức 1. Công thức COUNTIFS với những tiêu chí khác nhau

Giả sử chúng ta tất cả một danh sách thành phầm như trong hình dưới. quý khách ý muốn biết số mặt hàng tồn kho (giá trị cột B to hơn 0) tuy thế không bán tốt (cực hiếm cột C bằng 0).

Nhiệm vụ rất có thể được tiến hành bằng cách áp dụng bí quyết này:

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

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

*

Công thức 2. Công thức COUNTIFS cùng với các tiêu chuẩn như là nhau

Khi bạn muốn đếm các mục tất cả các tiêu chuẩn giống nhau, chúng ta vẫn phải hỗ trợ mỗi cặp criteria_range / criteria một bí quyết trơ khấc.

Ví dụ: đây là cách làm đúng nhằm đếm những 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ỉ gồm “Grapes” có mức giá trị “0” trong cả hai cột.

*

Sử dụng một cách làm đơn giản hơn với cùng một criteria_range độc nhất vô nhị nhỏng COUNTIFS(B2: C7, “= 0”) sẽ mang lại hiệu quả không giống – toàn bô những ô tính trong tầm B2:C7 đựng 0 (vào ví dụ này là 4).

Cách đếm số ô tính với rất nhiều tiêu chuẩn (OR logic):

Nhỏng các bạn đang thấy vào ví dụ trên, câu hỏi đếm các ô tính đáp ứng tất cả những tiêu chí hướng đẫn khôn cùng dễ dãi chính vì hàm COUNTIFS được thiết kế với nhằm vận động theo cách này.

Nhưng nếu như bạn có nhu cầu đếm những ô bao gồm ít nhất một trong những tiêu chuẩn được chỉ định và hướng dẫn là TRUE, tức thị dựa trên xúc tích OR thì sao? Nhìn tầm thường, bao gồm nhì phương pháp để thực điều này – bằng cách thêm một số trong những bí quyết 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 hai hoặc các công thức COUNTIF hoặc COUNITFS

Trong bảng dưới, giả sử bạn muốn tính những solo mua hàng với trạng thái “Cancelled” với “Pending“, chúng ta các bạn chỉ có thể viết 2 cách làm COUNTIF bình thường với dấ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 chứa đựng nhiều rộng một ĐK, sử dụng COUNTIFS nỗ lực vì chưng COUNTIF. Ví dụ: nhằm đếm số solo đặt hàng “Cancelled”  và “Pending” so với “Apples”, sử dụng phương pháp 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 tình huống các bạn quan trọng ập những tiêu chuẩn, phương pháp tiếp cận trên không hẳn là biện pháp buổi tối ưu bởi cách làm của các bạn sẽ vượt dài dòng. Để triển khai các phnghiền tính tương tự như trong một bí quyết nhỏ tuổi gọn gàng hơn, hãy liệt kê toàn bộ các tiêu chí của bạn trong mảng và hỗ trợ mảng kia đến criteria của hàm COUNTIFS. Để đếm tổng, phối kết hợp hàm COUNTIFS phía bên trong hàm SUM nhỏng sau:

=SUM (COUNTIFS (range, “criteria1”, “criteria2”, “criteria3”, …))

Trong bảng mẫu mã của chúng tôi, nhằm đếm các đơn đặt đơn hàng gồm trạng thái “Cancelled“, “Pending” hoặc “In transit“, công thức đang nlỗi sau:

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

*

Tương trường đoản cú, chúng ta cũng có thể đếm những ô dựa trên hai hoặc nhiều hơn những cặp criteria_range/criteria. Ví dụ: để hiểu con số 1-1 mua hàng “Apples” trong tâm trạng “Cancelled”, “Pending”, “In transit”, sử dụng phương pháp này:

=SUM(COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,“Cancelled”,”Pending”,”In transit”))

*

Cách đếm số trong khoảng cố kỉnh thể:

Công thức 1. Hàm COUNTIFS đếm các ô trong vòng số

Để tìm ra bao gồm từng nào số thân 5 cùng 10 (không bao gồm 5 và 10) được đựng trong những ô C2 cho C10, sử dụng cách làm này:

=COUNTIFS(C2:C10,”>5″,C2:C10,”=5″,B2:B10,”5″)-COUNTIF(C2:C10,”=10″) – đếm tất cả từng nào số lớn hơn 5 bé dại rộng 10 vào vùng tài liệu C2:C10.

Xem thêm: Cách Trị Bệnh Hôi Nach Hiệu Quả Nhất, Mẹo Dân Gian Chữa Hôi Nách

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – phương pháp đếm có bao nhiêu số trong đoạn tự 5 mang đến 10 thuộc vùng dữ iệu C2:C10, bao hàm cả 5 cùng 10.

Cách sử dụng tsi mê chiếu ô vào bí quyết COUNTIFS

khi áp dụng các toán thù tử lô ghích nlỗi “>”, “=” cùng rất những tđam mê chiếu ô vào bí quyết COUNTIFS, chú ý hãy đặt các tân oán tử vào ngoặc kép và thêm vết (&) trước tsay đắm chiếu ô để xây cất một chuỗi văn uống bạn dạng.

Trong tập tài liệu mẫu mã sau đây, hãy tính các giao dịch “Apples” với số tiền to hơn $200. Với criteria_range1 vào A2: A11 cùng criteria_range2 vào B2: B11, chúng ta có thể thực hiện 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 quý hiếm tiêu chí cùng phần đa ô nhất mực, ví dụ F1 cùng F2, tiếp nối tđê mê chiếu đa số ô kia trong cách làm của bạn:

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

Chú ý sử dụng quý giá tuyệt đối hoàn hảo vào criteria với criteria_range để phương pháp vẫn đúng vào khi sao chép thanh lịch các ô tính không giống.

*

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 các ký kết từ đại diện sau đây:

Dấu hỏi (?) – khớp cùng với ngẫu nhiên cam kết từ 1-1 nào, thực hiện nó để đếm những ô bước đầu hoặc xong bằng một số trong những ký tự cố định.

Dấu hoa thị (*) – khớp cùng với bất kỳ dãy cam kết tự làm sao, bạn sử dụng nó nhằm đếm ô chứa một tự ví dụ hoặc một k1 tự vào ô.

Lưu ý. Nếu bạn muốn đếm các ô chứa dấu hỏi hoặc dấu hoa thị, gõ vết bổ (~) trước dấu hoa thị hoặc dấu chấm hỏi.

Giả sử bạn bao gồm list các dự án vào cột A. Bạn ước ao biết bao gồm bao nhiêu dự án đã ký kết, Tức là cột B bao gồm cất tên người. Hãy thêm một điều kiện vật dụng hai – End date vào cột D.

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

Lưu ý, các bạn chẳng thể áp dụng ký kết trường đoản cú đại diện thay mặt trong tiêu chí thứ 2 do 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 do vì sao chúng ta sử dụng tiêu chuẩn “”&”” nhằm tra cứu ô chứa tài liệu.

*

COUNTIFS cùng COUNTIF với nhiều tiêu chuẩn ngày tháng:

lấy ví dụ như 1. Đếm ngày vào một khoảng chừng thời hạn cầm thể

Để đếm ngày trong một khoảng chừng thời hạn rõ ràng, bạn cũng có thể áp dụng công thức COUNTIFS cùng với nhì tiêu chuẩn hoặc kết hợp nhị hàm COUNTIF cùng nhau.

*

lấy ví dụ 3. Đếm ngày với nhiều ĐK dựa trên ngày hiện tại tại

quý khách có thể áp dụng hàm TODAY kết hợp với COUNTIF để tính các ngày dựa trên ngày từ bây giờ.

Ví dụ: công thức COUNTIF sau cùng với nhị vùng tài liệu và nhị tiêu chuẩn đã cho mình biết bao gồm từng nào thành phầm đã có được mua tuy thế không giao hàng:

=COUNTIFS(C2:C9,””&TODAY())

*

Công thức này hoàn toàn có thể chứa được nhiều vươn lên là. Ví dụ: bạn có thể tinh chỉnh và điều khiển nó để đếm gồm bao nhiêu thành phầm được thiết lập rộng một tuần trước đó và chưa giao hàng:

=COUNTIFS(C2:C9,””&TODAY())

bởi vậy cùng với bài viết này, dramrajani.com.hocexecl.online vẫn chia sẻ cũng tương tự trả lời đến chúng ta một giải pháp áp dụng cách làm CountIFS cùng CountIF trong Excel một bí quyết cụ thể tuyệt nhất, giả dụ tất cả ngẫu nhiên vướng mắc như thế nào các bạn hãy comment ngay lập tức làm việc dưới nội dung bài viết để Cửa Hàng chúng tôi rất có thể xử lý hầu như vướng mắc của chúng ta một cách nhanh nhất có thể.