LỆNH ĐẾM CÓ ĐIỀU KIỆN TRONG EXCEL

Trong nội dung bài viết này, Học Excel Online sẽ giải đáp cách thực hiện công thức Hàm COUNTIFS và thực hiện Hàm COUNTIF trong Excel dựa trên thuật ngắn gọn xúc tích AND OR. Các bạn sẽ tìm thấy rất nhiều ví dụ với các kiểu dữ liệu không giống nhau – số, ngày tháng, text với ô cất dữ liệu.

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


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 – xác minh phạm vi trước tiên áp dụng điều kiện trước tiên (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à hoàn toàn 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 đang hiển thị các đối số của hàm ngay khi bạn bắt đầu gõ. Đối số được nhập vào hiện tại sẽ được tô đậm.

*

COUNTIFS – phần nhiều điều cần lưu lại ý:

Bạn có thể sử dụng hàm COUNTIFS nhằm đếm các ô vào một phạm vi riêng biệt với một đk duy nhất cũng giống như trong nhiều vùng dữ liệu với nhiều điều kiện. Vào trường hợp thiết bị hai, chỉ đầy đủ ô tính đáp ứng toàn bộ các điều kiện new được đếm.Mỗi vùng dữ liệu bổ sung cập nhật phải bao gồm cùng số hàng cùng cột như vùng dữ liệu trước tiên (đối số criteria_range1).Có thể đếm đông đảo vùng tài liệu liền kề không liền kề.Nếu tiêu chuẩn là một tham chiếu cho một ô trống, hàm COUNTIFS xử trí nó như một giá bán trị bằng không (0).Bạn hoàn toàn có thể sử dụng các wildcard characters trong các tiêu chí – vệt hoa thị (*) cùng dấu chấm hỏi (?).

Cách áp dụng hàm COUNTIFS và COUNTIF trong excel với rất 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. Cách làm COUNTIFS 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 bên dưới. Bạn có nhu cầu biết số sản phẩm tồn kho (giá trị cột B to hơn 0) tuy thế 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ách làm này:

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

Kết quả nhận thấy là 2 (“Cherries”“Lemons”):

*

Công thức 2. Cách làm COUNTIFS với các tiêu chí giống nhau

Khi bạn muốn đếm những mục có các tiêu chuẩn giống nhau, bạn vẫn phải cung cấp mỗi cặp criteria_range / criteria một giải pháp riêng lẻ.

Ví dụ: đây là công thức đúng để đế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ưng chỉ bao gồm “Grapes” có giá trị “0” vào cả nhị cột.

*

Sử dụng một công thức dễ dàng và đơn giản hơn với 1 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 khoảng B2:C7 chứa 0 (trong lấy ví dụ này là 4).

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

Như các bạn đã thấy trong ví dụ như trên, vấn đề đếm các ô tính đáp ứng tất cả các tiêu chuẩn chỉ định rất đơn giản dàng bởi vì hàm COUNTIFS được thiết kế để hoạt động theo giải pháp này.

Nhưng nếu bạn có nhu cầu đếm những ô bao gồm ít nhất 1 trong các tiêu chí được hướng đẫn là TRUE, tức thị dựa trên ngắn gọn xúc tích OR thì sao? nhìn chung, có hai phương pháp để thực điều này – bằng phương pháp thêm một số công thức COUNTIF hoặc thực hiện 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 mặt dưới, mang sử bạn muốn tính các đơn đặt đơn hàng với tinh thần “Cancelled” với “Pending“, các bạn bạn chỉ rất có thể viết 2 bí quyết COUNTIF thông thường và nhận 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 nhiều hơn một điều kiện, áp dụng COUNTIFS thay vì chưng COUNTIF. Ví dụ: nhằm đếm số đơn đặt đơn hàng “Cancelled”  và “Pending” đối cùng 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ông thức 2. Phương pháp SUM COUNTIFS cùng với hằng mảng

Trong tình huống bạn quan trọng ập các tiêu chí, bí quyết tiếp cận trên chưa hẳn là cách tối ưu bởi công thức của các bạn sẽ quá nhiều năm dòng. Để triển khai 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ê tất cả các tiêu chí của khách hàng trong mảng và hỗ trợ mảng đó mang lại criteria của hàm COUNTIFS. Để đếm tổng, kết hợp hàm COUNTIFS phía bên trong hàm SUM như sau:

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

Trong bảng mẫu của bọn chúng tôi, để đếm các đơn đặt 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 những ô dựa trên hai hoặc nhiều hơn thế nữa các cặp criteria_range/criteria. Ví dụ: nhằm biết số lượng đơn mua hàng “Apples” trong tinh thần “Cancelled”, “Pending”, “In transit”, sử 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 đếm số trong vòng cụ thể:

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

Để tìm ra có bao nhiêu số giữa 5 và 10 (không bao hà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ố to hơn 5 bé dại hơn 10 vào vùng tài liệu C2:C10.

Xem thêm: 10 Phần Mềm Chỉnh Sửa Video Tốt Nhất Trên Máy Tính, Top 6 Phần Mềm Chỉnh Sửa Video Miễn Phí Tốt Nhất

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – phương pháp đếm gồm bao nhiêu số trong khúc từ 5 cho 10 nằm trong vùng dữ iệu C2:C10, bao gồm cả 5 và 10.

Cách áp dụng tham chiếu ô trong phương pháp 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 ô trong bí quyết COUNTIFS, để ý hãy đặt những toán tử vào ngoặc kép và thêm lốt (&) trước tham chiếu ô để chế tạo một chuỗi văn bản.

Trong tập dữ liệu mẫu 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 vào A2: A11 với criteria_range2 vào B2: B11, bạn 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, chúng ta có thể nhập những giá trị tiêu chuẩn và rất nhiều ô tốt nhất định, lấy ví dụ F1 với F2, kế tiếp tham chiếu số đông ô kia trong phương pháp của bạn:

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

Chú ý áp dụng giá trị tuyệt đối trong criteria criteria_range để phương pháp vẫn đúng khi xào luộc sang các ô tính khác.

*

Cách áp dụng COUNTIFS với các ký trường đoản cú đại diện:

Trong cách làm Excel, chúng ta có thể sử dụng những ký tự thay mặt đại diện sau đây:

Dấu hỏi (?) – khớp với ngẫu nhiên ký tự solo nào, áp dụng nó nhằm đếm các ô ban đầu hoặc kết thúc bằng một số ký tự duy nhất định.

Dấu hoa thị (*) – khớp với ngẫu nhiên dãy cam kết tự nào, bạn dùng nó để đếm ô cất một từ cụ thể hoặc một k1 tự vào ô.

Lưu ý. Nếu bạn có nhu cầu đếm những ô chứa dấu hỏi hoặc có thể dấu hoa thị, gõ dấu bửa (~) 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 những dự án vào cột A. Bạn muốn biết gồm bao nhiêu dự án công trình đã ký 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ứ hai – End date trong cột D.

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

Lưu ý, bạn không thể sử dụng ký tự thay mặt trong tiêu chí thứ 2 vị cột D cất kiểu tài liệu ngày tháng chứ không phải dạng text. Đó là lý do tại sao bạn sử dụng tiêu chuẩn “”&”” nhằm tìm ô đựng dữ liệu.

*

COUNTIFS với 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 vào một khoảng thời hạn cụ thể, bạn có thể sử dụng cách làm COUNTIFS với hai tiêu chuẩn hoặc kết hợp hai hàm COUNTIF cùng với nhau.

*

Ví dụ 3. Đếm ngày với rất nhiều điều kiện dựa vào ngày hiện tại

Bạn rất có thể sử dụng hàm TODAY kết phù hợp với COUNTIF nhằm tính các ngày dựa trên ngày hôm nay.

Ví dụ: bí quyết COUNTIF sau với nhì vùng dữ liệu và hai tiêu chí sẽ cho bạn biết tất cả bao nhiêu sản phẩm đã được tải nhưng không giao hàng:

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

*

Công thức này có thể chứa nhiều biến. Ví dụ: bạn cũng có thể tinh chỉnh nó nhằm đếm tất cả bao nhiêu sản phẩm được mua hơn một tuần trước và chưa giao hàng:

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

Như vậy với bài viết này, huyenkhuong.com.hocexecl.online đã share cũng như hướng dẫn cho chúng ta một cách sử dụng công thức CountIFS và CountIF trong Excel một cách cụ thể nhất, nếu như 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 để công ty 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 cách sớm nhất.