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

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


Hàm COUNTIFS – phương pháp và giải pháp sử dụng

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

=COUNTIFS (criteria_range1, criteria1, …)

criteria_range1 – khẳng định phạm vi trước tiên áp dụng điều kiện đầu tiên (criteria1), bắt buộc.

criteria1 – đặt đk 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 khẳng định những ô tính nào sẽ tiến hành đế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 vẫn hiển thị những đối số của hàm ngay lúc bạn bắt đầu gõ. Đối số được nhập vào hiện tại sẽ tiến hành tô đậm.

*

COUNTIFS – gần như điều cần giữ ý:

Bạn có thể sử dụng hàm COUNTIFS để đếm những ô trong một phạm vi đơn lẻ với một điều kiện duy nhất tương tự như trong các vùng dữ liệu với khá nhiều điều kiện. Vào trường hợp lắp thêm hai, chỉ mọi ô tính đáp ứng 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 tất cả cùng số hàng và cột như vùng dữ liệu trước tiên (đối số criteria_range1).Có thể đếm mọi vùng tài liệu liền kề không liền kề.Nếu tiêu chí là một tham chiếu cho một ô trống, hàm COUNTIFS cách xử trí nó như một giá bán trị bằng không (0).Bạn rất có thể sử dụng những wildcard characters trong các tiêu chuẩn – vết hoa thị (*) cùng dấu chấm hỏi (?).

Cách sử dụng hàm COUNTIFS với COUNTIF trong excel với khá 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. Bí quyết COUNTIFS với các tiêu chí khác nhau

Giả sử bạn có một danh sách sản phẩm như vào 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 lớn hơn 0) mà lại chưa bán được (giá trị cột C bằng 0).

Nhiệm vụ có thể được thực hiện bằng phương pháp sử dụng công thức 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. Bí quyết COUNTIFS với các tiêu chí giống nhau

Khi bạn có nhu cầu đếm các mục gồm các tiêu chí giống nhau, các bạn vẫn phải cung ứng mỗi cặp criteria_range / criteria một giải pháp riêng lẻ.

Ví dụ: đó là công thức đú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 bởi vì chỉ có “Grapes” có mứ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 như COUNTIFS(B2: C7, “= 0”) sẽ có lại hiệu quả khác – tổng số những ô tính trong vòng B2:C7 cất 0 (trong ví dụ này là 4).

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

Như chúng ta đã thấy trong ví dụ như trên, câu hỏi đếm các ô tính đáp ứng tất cả các tiêu chí chỉ định rất dễ dàng cũng chính vì hàm COUNTIFS được thiết kế để hoạt động theo cách này.

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

Trong bảng mặt dưới, trả sử bạn muốn tính những đơn đặt đơn hàng với tinh thần “Cancelled” cùng “Pending“, các bạn bạn chỉ có thể viết 2 cách làm 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 chứa nhiều hơn một điều kiện, sử dụng COUNTIFS thay vì COUNTIF. Ví dụ: để đếm số đơn đặt hàng “Cancelled”  và “Pending” đối cùng với “Apples”, áp 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. Bí quyết SUM COUNTIFS cùng với hằng mảng

Trong tình huống bạn cần thiết ập những tiêu chí, biện pháp tiếp cận trên không phải là biện pháp tối ưu bởi bí quyết của các bạn sẽ quá lâu năm dòng. Để thực hiện các phép tính tựa như trong một công thức nhỏ gọn 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 đó đến criteria của hàm COUNTIFS. Để đếm tổng, phối hợp hàm COUNTIFS phía bên trong hàm SUM như sau:

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

Trong bảng chủng loại của chúng tôi, để đếm các đơn mua hàng có tinh thần “Cancelled“, “Pending” hoặc “In transit“, cách làm sẽ như sau:

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

*

Tương tự, bạn cũng có thể đếm những ô dựa vào hai hoặc nhiều hơn thế các cặp criteria_range/criteria. Ví dụ: nhằm biết số lượng đơn đặt hàng “Apples” trong tâm lý “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 tất cả bao nhiêu số thân 5 cùng 10 (không bao gồm 5 cùng 10) được chứa 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 có bao nhiêu số lớn hơn 5 nhỏ tuổi hơn 10 trong vùng tài liệu C2:C10.

Xem thêm: 50+ Hình Nền Tình Yêu Lãng Mạn Full Hd Đẹp Nhất Cho Bạn Đọc, Hình Nền Tình Yêu Lãng Mạn, Ngọt Ngào, Đẹp Nhất

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

Cách áp dụng tham chiếu ô trong bí quyết COUNTIFS

Khi sử dụng các toán tử xúc tích như “>”, “=” thuộc với những tham chiếu ô trong công thức COUNTIFS, chú ý hãy đặt những toán tử vào ngoặc kép cùng thêm lốt (&) trước tham chiếu ô để thiết kế một chuỗi văn bản.

Trong tập tài liệu mẫu dưới đây, hãy tính các deals “Apples” cùng với số tiền lớn hơn $200. Cùng với criteria_range1 trong A2: A11 với criteria_range2 vào 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 chí và gần như ô tốt nhất định, lấy ví dụ F1 cùng F2, tiếp nối tham chiếu hầu hết ô đó trong công thức 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 những ô tính khác.

*

Cách sử dụng COUNTIFS với các ký tự đại diện:

Trong bí quyết Excel, bạn cũng có thể sử dụng các ký tự thay mặt đại diện sau đây:

Dấu hỏi (?) – khớp với bất kỳ ký tự đối chọi nào, áp dụng nó để đếm những ô bước đầu hoặc kết thúc bằng một vài ký tự tốt nhất định.

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

Lưu ý. Nếu bạn muốn đếm những ô cất dấu hỏi hoặc giấu hoa thị, gõ dấu vấp ngã (~) trước vết hoa thị hoặc dấu chấm hỏi.

Giả sử bạn có danh sách các dự án trong cột A. Bạn muốn biết bao gồm bao nhiêu dự án công trình đã ký kết 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 điều kiện thứ hai – End date trong cột D.

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

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

*

COUNTIFS cùng 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 bí quyết COUNTIFS với hai tiêu chí 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ụ: công thức COUNTIF sau với hai vùng dữ liệu và hai tiêu chí sẽ cho bạn biết tất cả bao nhiêu thành phầm đã được cài nhưng chưa giao hàng:

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

*

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

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

Như vậy với nội dung bài viết này, socialgame.vn.hocexecl.online đã share cũng như lí giải cho các bạn một cách áp dụng công thức CountIFS cùng CountIF vào Excel một cách cụ thể nhất, ví như có bất kỳ thắc mắc nào chúng ta hãy bình luận ngay ở mặt dưới bài viết để shop chúng tôi có thể giải quyết và xử lý mọi thắc mắc của bạn một bí quyết sớm nhất.