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

Bạn đang xem: Cách dùng hàm countif kết hợp với hàm and

Hàm COUNTIFS – cách làm và biện 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 đầu tiên áp dụng điều kiện trước 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 chuẩn này xác minh 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ị các đối số của hàm ngay trong lúc bạn bước đầu gõ. Đối số được nhập vào hiện tại tại sẽ được tô đậm.

COUNTIFS – mọi điều cần giữ ý:

chúng ta có thể sử dụng hàm COUNTIFS nhằm đếm các ô trong một phạm vi đơn lẻ với một đk duy nhất tương tự như trong những vùng dữ liệu với khá nhiều điều kiện. Trong trường hợp sản phẩm hai, chỉ đầy đủ ô tính đáp ứng tất cả các điều kiện mới được đếm. Mỗi vùng dữ liệu bổ sung cập nhật phải có cùng số hàng và cột như vùng dữ liệu đầu tiên (đối số criteria_range1). Có thể đếm những vùng tài liệu liền kề và ko liền kề. Nếu tiêu chuẩn là một tham chiếu mang đến 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 có thể sử dụng những wildcard characters trong các tiêu chuẩn – lố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 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 cùng với các tiêu chuẩn khác nhau

Giả sử chúng ta có một danh sách sản phẩm như vào hình bên dưới. Bạn muốn biết số hàng tồn kho (giá trị cột B lớn hơn 0) tuy nhiên chưa bán tốt (giá trị cột C bằng 0).

Nhiệm vụ rất 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ả cảm nhận là 2 (“Cherries” với “Lemons”):

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

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

Ví dụ: đó là công thức đúng để đếm các mục chứa 0 cả vào 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ư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 hơn với cùng một criteria_range duy nhất như COUNTIFS(B2: C7, “= 0”) sẽ mang lại tác dụng khác – tổng số những ô tính trong tầm B2:C7 chứa 0 (trong lấy một ví dụ này là 4).

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

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

Trong bảng mặt dưới, trả sử bạn muốn tính các đơn mua hàng với tâm lý “Cancelled” cùng “Pending“, chúng ta bạn chỉ có thể viết 2 công thức COUNTIF bình thường và nhấn kết quả:

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

Trong trường hòa hợp mỗi hàm chứa nhiều hơn một điều kiện, sử dụng COUNTIFS thay vì chưng COUNTIF. Ví dụ: để đếm số đơn mua hàng “Cancelled”  và “Pending” đối cùng với “Apples”, sử 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ách làm SUM COUNTIFS cùng với hằng mảng

Trong trường hợp bạn cần thiết ập những tiêu chí, bí quyết tiếp cận trên chưa phải là bí quyết tối ưu bởi phương pháp của các bạn sẽ quá lâu năm dòng. Để triển khai các phép tính tựa 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 chúng ta trong mảng và cung ứng 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 chúng tôi, để đếm những đơ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ự, bạn có thể đếm những ô dựa trên hai hoặc nhiều hơn thế các cặp criteria_range/criteria. Ví dụ: để biết con số đơn mua hàng “Apples” trong tâm trạng “Cancelled”, “Pending”, “In transit”, thực hiện 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 tầm cụ thể:

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

Để tìm kiếm ra bao gồm bao nhiêu số giữa 5 với 10 (không bao hàm 5 với 10) được chứa trong số ô C2 cho C10, thực hiện 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ố to hơn 5 nhỏ dại hơn 10 vào vùng tài liệu C2:C10.

Xem thêm: Bật Mí 3 Cách Dùng Điện Thoại Làm Tay Cầm Chơi Game Trên Máy Vi Tính

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

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

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

Trong tập dữ liệu mẫu bên dưới đây, hãy tính các đơn hàng “Apples” cùng với số tiền lớn hơn $200. Cùng với criteria_range1 trong A2: A11 và 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 những giá trị tiêu chí và đa số ô độc nhất định, lấy ví dụ F1 cùng F2, kế tiếp tham chiếu phần lớn ô đó trong phương pháp của bạn:

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

Chú ý sử dụng giá trị tuyệt đối trong criteria và criteria_range để phương pháp vẫn đúng khi sao chép sang những ô tính khác.

Cách áp dụng COUNTIFS với những ký từ đại diện:

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

Dấu hỏi (?) – khớp với bất kỳ ký tự 1-1 nào, sử dụng nó để đếm những ô ban đầu hoặc kết thúc bằng một số trong những ký tự tuyệt nhất định.

Dấu hoa thị (*) – khớp với bất kỳ dãy ký kết tự nào, chúng ta dùng nó để đếm ô cất một từ rõ ràng hoặc một k1 tự vào ô.

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

Giả sử bạn 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 kết, tức 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ự đại diện thay mặt trong tiêu chí thứ 2 vày cột D chứa kiểu tài liệu ngày mon chứ chưa hẳn dạng text. Đó là vì sao tại sao các bạn sử dụng tiêu chuẩn “”&”” để tìm ô chứa dữ liệu.

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

Ví dụ 1. Đếm ngày trong một khoảng thời gian cụ thể

Để đếm ngày trong một khoảng thời hạn cụ thể, bạn có thể sử dụng công thức COUNTIFS cùng với hai tiêu chuẩn hoặc phối hợp hai hàm COUNTIF cùng với nhau.

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

Bạn hoàn toàn có thể sử dụng hàm TODAY kết 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ách làm COUNTIF sau với nhì vùng tài liệu và hai tiêu chuẩn sẽ cho mình biết gồm bao nhiêu thành phầm đã được thiết lập 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ụ: chúng ta có thể tinh chỉnh nó nhằm đếm có bao nhiêu thành phầm được cài đặt 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, baf.edu.vn.hocexecl.online đã chia sẻ cũng như giải đáp cho các bạn một cách sử dụng công thức CountIFS và CountIF trong Excel một cách cụ thể nhất, giả dụ có ngẫu nhiên thắc mắc nào chúng ta hãy comment ngay ở bên dưới bài viết để công ty chúng tôi có thể giải quyết mọi thắc mắc của công ty một bí quyết sớm nhất.