Đếm có điều kiện trong excel

Trong bài viết này, Học Excel Online đang gợi ý cách sử dụng bí quyết Hàm COUNTIFS với sử dụng Hàm COUNTIF trong Excel dựa vào thuật xúc tích AND cùng OR. quý khách đang search thấy không hề ít ví dụ cùng với những đẳng cấp tài liệu không giống nhau – số, tháng ngày, text cùng ô chứa dữ liệu.

Bạn đang xem: Đếm có điều kiện trong excel

Hàm COUNTIFS – cách làm và cách sử dụng

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

=COUNTIFS (criteria_range1, criteria1, …)

criteria_range1 – xác định phạm vi thứ nhất vận dụng điều kiện đầu tiên (criteria1), cần.

criteria1 – đặt ĐK cho dạng số, ô tđắm đuối chiếu, chuỗi văn uống bản, một mảng hoặc một hàm Excel khác, yêu cầu. Tiêu chí này khẳng định phần đa ô tính nào sẽ được đếm với rất có thể màn biểu 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 đang hiển thị các đối số của hàm tức thì khi chúng ta bắt đầu gõ. Đối số được nhtràn lên bây giờ sẽ được đánh đậm.

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

Bạn rất có thể áp dụng hàm COUNTIFS để đếm các ô vào một phạm vi cá biệt với một điều kiện độc nhất vô nhị tương tự như trong vô số nhiều vùng tài liệu với khá nhiều ĐK. Trong trường hòa hợp lắp thêm hai, chỉ phần đông ô tính đáp ứng tất cả những điều kiện mới được đếm.Mỗi vùng tài liệu bổ sung cập nhật yêu cầu có thuộc số mặt hàng với cột như vùng tài liệu thứ nhất (đối số criteria_range1).Có thể đếm phần đa vùng dữ liệu gần kề và ko ngay thức thì kề.Nếu tiêu chuẩn là 1 trong tham chiếu đến một ô trống, hàm COUNTIFS cách xử trí nó nhỏng một quý giá bởi ko (0).Bạn có thể sử dụng các wildcard characters trong số tiêu chuẩn – vệt hoa thị (*) cùng vết chnóng hỏi (?).

Cách thực hiện hàm COUNTIFS và COUNTIF trong excel với tương đối nhiều tiêu chí:

Cách đếm ô tính với tương đối nhiều tiêu chí (AND logic):

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

Giả sử chúng ta gồm một list thành phầm như vào hình bên dưới. quý khách ao ước biết số mặt hàng tồn kho (quý hiếm cột B to hơn 0) tuy nhiên không bán tốt (quý giá cột C bằng 0).

Nhiệm vụ rất có thể được tiến hành bằng phương pháp sử dụng cách làm 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 những tiêu chuẩn như là nhau

Khi bạn có nhu cầu đếm những mục gồm các tiêu chuẩn kiểu như nhau, bạn vẫn đề nghị cung cấp từng cặp criteria_range / criteria một biện pháp đơn côi.

Ví dụ: đó là công thức đúng nhằm đếm các mục cất 0 cả vào 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ỉ 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 và đơn giản rộng với cùng một criteria_range độc nhất nlỗi COUNTIFS(B2: C7, “= 0”) đang mang đến tác dụng khác – tổng số các ô tính trong vòng B2:C7 cất 0 (vào ví dụ này là 4).

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

Nhỏng các bạn sẽ thấy vào ví dụ bên trên, câu hỏi đếm các ô tính thỏa mãn nhu cầu tất cả những tiêu chí chỉ định hết sức dễ dàng chính vì hàm COUNTIFS được thiết kế để vận động Theo phong cách này.

Nhưng ví như bạn có nhu cầu đếm những ô bao gồm tối thiểu một trong các tiêu chí được hướng đẫn là TRUE, tức thị dựa trên logic OR thì sao? Nhìn tầm thường, gồm nhị cách để thực điều đó – bằng phương pháp thêm một số bí quyết COUNTIF hoặc thực hiện phương pháp SUM COUNTIFS cùng với hằng số mảng.

Công thức 1. Thêm nhì hoặc những bí quyết COUNTIF hoặc COUNITFS

Trong bảng dưới, mang sử bạn có nhu cầu tính những đối kháng mua hàng với trạng thái “Cancelled” với “Pending“, chúng ta các bạn chỉ rất có thể viết 2 phương pháp COUNTIF thông thường và nhận kết quả:

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

Trong ngôi trường đúng theo mỗi hàm đựng nhiều rộng một ĐK, sử dụng COUNTIFS cầm do COUNTIF. Ví dụ: để đếm số đối kháng đặt đơn hàng “Cancelled”  và “Pending” đối với “Apples”, thực hiện bí quyết 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 cùng với hằng mảng

Trong tình huống chúng ta cần thiết ập nhiều tiêu chí, giải pháp tiếp cận trên chưa phải là bí quyết về tối ưu do phương pháp của các bạn sẽ vượt lâu năm chiếc. Để tiến hành những phép tính tựa như vào một công thức bé dại gọn hơn, hãy liệt kê toàn bộ những tiêu chí của công ty trong mảng với hỗ trợ mảng đó đến criteria của hàm COUNTIFS. Để đếm tổng, phối kết hợp hàm COUNTIFS bên phía trong hàm SUM nhỏng sau:

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

Trong bảng mẫu của Shop chúng tôi, để đếm những đối kháng mua hàng có tâm trạng “Cancelled“, “Pending” hoặc “In transit“, công thức vẫn nlỗi sau:

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

Tương trường đoản cú, bạn cũng có thể đếm những ô dựa trên nhị hoặc nhiều hơn thế những cặp criteria_range/criteria. Ví dụ: để biết con số đối kháng mua hàng “Apples” vào tâm lý “Cancelled”, “Pending”, “In transit”, sử dụng bí quyết này:

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

Cách đếm số trong tầm cố kỉnh thể:

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

Để tìm thấy bao gồm bao nhiêu số thân 5 và 10 (không bao hàm 5 với 10) được đựng trong số ô 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 tất cả từng nào số lớn hơn 5 nhỏ hơn 10 trong vùng dữ liệu C2:C10.

Xem thêm: Vị Trí Đập Tam Hiệp Nằm Ở Đâu Trên Bản Đồ, Vị Trí Đập Tam Hiệp Trên Bản Đồ Nằm Ở Đâu

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – công thức đếm bao gồm bao nhiêu số trong khúc từ 5 cho 10 thuộc vùng dữ iệu C2:C10, bao gồm cả 5 với 10.

Cách thực hiện tham mê chiếu ô trong phương pháp COUNTIFS

Lúc áp dụng các tân oán tử súc tích nlỗi “>”, “=” với những tmê man chiếu ô vào cách làm COUNTIFS, lưu ý hãy đặt các tân oán tử trong ngoặc knghiền và thêm lốt (&) trước tđam mê chiếu ô nhằm xây cất một chuỗi văn bạn dạng.

Trong tập dữ liệu chủng loại tiếp sau đây, hãy tính những deals “Apples” với số tiền lớn hơn $200. Với criteria_range1 vào A2: A11 với criteria_range2 vào B2: B11, bạn 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ó thể nhập các quý hiếm tiêu chuẩn với các ô khăng khăng, ví dụ F1 và F2, tiếp đến tmê mệt chiếu phần đông ô 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 trong criteria cùng criteria_range nhằm phương pháp vẫn đúng vào khi sao chép lịch sự các ô tính không giống.

Cách sử dụng COUNTIFS cùng với những ký từ bỏ đại diện:

Trong bí quyết Excel, bạn cũng có thể thực hiện những cam kết từ bỏ thay mặt sau đây:

Dấu hỏi (?) – khớp với bất kỳ ký kết trường đoản cú đối kháng làm sao, thực hiện nó để đếm những ô bước đầu hoặc xong bằng một số trong những ký kết tự nhất định.

Dấu hoa thị (*) – khớp cùng với bất kỳ hàng ký kết trường đoản cú như thế nào, chúng ta sử dụng nó để đếm ô đựng một từ bỏ cụ thể hoặc một k1 tự vào ô.

Lưu ý. Nếu bạn có nhu cầu đếm các ô cất dấu hỏi hoặc che dấu hoa thị, gõ lốt bổ (~) trước lốt hoa thị hoặc giấu chnóng hỏi.

Giả sử các bạn tất cả danh sách các dự án công trình vào cột A. Bạn mong biết tất cả bao nhiêu dự án công trình đã ký kết, Có nghĩa là cột B có chứa tên bạn. Hãy thêm 1 ĐK thiết bị nhì – End date trong cột D.

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

Lưu ý, chúng ta cần yếu áp dụng ký kết trường đoản cú đại diện thay mặt trong tiêu chí thứ hai vày cột D cất đẳng cấp tài liệu tháng ngày chứ chưa phải dạng text. Đó là nguyên do tại sao chúng ta thực hiện tiêu chuẩn “”&”” để tìm kiếm ô cất tài liệu.

COUNTIFS với COUNTIF với rất nhiều tiêu chuẩn ngày tháng:

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

Để đếm ngày trong một khoảng thời gian rõ ràng, chúng ta cũng có thể sử dụng cách làm COUNTIFS với nhì tiêu chí hoặc kết hợp hai hàm COUNTIF với nhau.

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

Quý Khách có thể áp dụng hàm TODAY kết phù hợp với COUNTIF để tính những ngày dựa vào 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ới nhị tiêu chuẩn đang cho mình biết tất cả bao nhiêu thành phầm đã làm được thiết lập mà lại không giao hàng:

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

Công thức này hoàn toàn có thể đựng được nhiều phát triển thành. Ví dụ: chúng ta có thể điều khiển nó nhằm đếm tất cả bao nhiêu thành phầm được cài đặt hơn một tuần trước đó với không giao hàng:

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

do vậy cùng với bài viết này, yeahflashback.com.hocexecl.online đã share cũng giống như lí giải cho chúng ta một cách thực hiện công thức CountIFS cùng CountIF trong Excel một biện pháp chi tiết duy nhất, giả dụ gồm ngẫu nhiên vướng mắc nào chúng ta hãy comment tức thì ngơi nghỉ dưới bài viết để Shop chúng tôi có thể xử lý số đông vướng mắc của người sử dụng một giải pháp nhanh nhất.