Sử dụng hàm countif trong excel

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

Bạn đang xem: Sử dụng hàm countif trong excel

Hàm COUNTIFS – cách làm và phương pháp 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 áp dụng điều kiện thứ nhất (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 khẳng định những ô tính nào sẽ tiến hành đếm và 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ị 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 nay tại sẽ được tô đậm.

COUNTIFS – đầy đủ điều cần lưu ý:

Bạn hoàn toàn có thể sử dụng hàm COUNTIFS nhằm đếm các ô trong một phạm vi đơn nhất với một điều kiện duy nhất cũng như trong nhiều vùng dữ liệu với khá nhiều điều kiện. Vào trường hợp máy hai, chỉ các ô tính đáp ứng toàn bộ các điều kiện bắt đầu được đếm.Mỗi vùng dữ liệu bổ sung cập nhật phải gồm cùng số hàng và cột như vùng dữ liệu thứ nhất (đối số criteria_range1).Có thể đếm đều 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 đến một ô trống, hàm COUNTIFS xử trí nó như một giá trị bằng không (0).Bạn hoàn toàn có thể sử dụng những wildcard characters trong các tiêu chí – dấu hoa thị (*) với dấu chấm hỏi (?).

Cách sử dụng hàm COUNTIFS cùng COUNTIF vào 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. Công thức COUNTIFS cùng với các tiêu chuẩn khác nhau

Giả sử các bạn có một danh sách thành phầm như vào hình mặt dưới. Bạn có nhu cầu biết số hàng tồn kho (giá trị cột B to hơn 0) nhưng mà chưa bán được (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ông thức này:

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

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

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

Khi bạn có nhu cầu đếm những mục bao gồm các tiêu chuẩn giống nhau, các bạn vẫn phải hỗ trợ mỗi cặp criteria_range / criteria một phương pháp riêng lẻ.

Ví dụ: đó là công thức đúng để đếm những mục cất 0 cả vào cột B cùng 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ỉ có “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 cùng một criteria_range duy tốt nhất như COUNTIFS(B2: C7, “= 0”) sẽ mang lại công dụng khác – tổng số những ô tính trong vòng B2:C7 đựng 0 (trong lấy một 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 lấy ví dụ như trên, việc đếm các ô tính đáp ứng tất cả các tiêu chí chỉ định rất dễ dàng dàng chính vì hàm COUNTIFS được thiết kế để chuyển động theo giải pháp này.

Nhưng nếu bạn có nhu cầu đếm các ô có ít nhất 1 trong các các tiêu chuẩn được hướng dẫn và chỉ định là TRUE, tức là dựa trên lô ghích OR thì sao? chú ý chung, gồm hai phương pháp để thực điều đó – bằng cách thêm một trong những 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 có nhu cầu tính những đơn mua hàng với tinh thần “Cancelled” và “Pending“, các bạn bạn chỉ rất có thể viết 2 công thức COUNTIF thông thường và dìm kết quả:

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

Trong trường phù hợp mỗi hàm chứa được nhiều hơn một điều kiện, áp dụng COUNTIFS thay do COUNTIF. Ví dụ: để đếm số đơn đặt hàng “Cancelled”  và “Pending” đối 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 trường hợp bạn quan trọng ập nhiều tiêu chí, phương pháp tiếp cận trên không phải là bí quyết tối ưu bởi phương pháp của bạn sẽ quá nhiều năm dòng. Để triển khai các phép tính tựa như trong một công thức bé 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 đó đến criteria của hàm COUNTIFS. Để đếm tổng, phối hợp hàm COUNTIFS 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, nhằm đếm các đơn mua hàng có tâm trạng “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ó thể đếm các ô 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 đặt đơn 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 tầm số

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

Xem thêm: Hướng Dẫn Sử Dụng Các Nút Điều Khiển Trên Xe Ô Tô Cần Ghi Nhớ Khi Học Lái Xe

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

Cách thực hiện tham chiếu ô trong công thức COUNTIFS

Khi sử dụng các toán tử logic như “>”, “=” cùng với những tham chiếu ô trong cách làm COUNTIFS, xem xét 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 ô để xây dựng 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 giao dịch “Apples” cùng với số tiền lớn hơn $200. Cùng với criteria_range1 vào A2: A11 với criteria_range2 vào B2: B11, bạn 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 các giá trị tiêu chuẩn và những ô duy nhất định, lấy một ví dụ F1 với F2, sau đó tham chiếu đa số ô đó 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 giá trị tuyệt đối trong criteria và criteria_range để cách làm vẫn đúng khi xào nấu sang các ô tính khác.

Cách thực hiện COUNTIFS với những ký tự đại diện:

Trong công thức Excel, chúng ta cũng có thể sử dụng các ký tự thay mặt sau đây:

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

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

Lưu ý. Nếu bạn có nhu cầu đếm những ô chứa dấu hỏi hoặc che dấu hoa thị, gõ dấu ngã (~) trước dấu hoa thị hoặc dấu chấm hỏi.

Giả sử các bạn có danh sách những 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, có nghĩa là cột B bao gồm chứa tên người. Hãy thêm một điều kiện thứ nhị – End date trong cột D.

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

Lưu ý, bạn không thể thực hiện ký tự thay mặt trong tiêu chuẩn thứ 2 do cột D đựng kiểu dữ liệu ngày tháng 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 và COUNTIF cùng với nhiều tiêu chuẩn ngày tháng:

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

Để đếm ngày vào một khoảng thời hạn cụ thể, chúng ta có thể sử dụng công thức COUNTIFS cùng với hai tiêu chuẩn hoặc kết hợp hai hàm COUNTIF 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 nay tại

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

Ví dụ: phương pháp COUNTIF sau với nhị vùng dữ liệu và hai tiêu chí sẽ cho chính mình biết gồm bao nhiêu sản phẩm đã được tải nhưng chưa giao hàng:

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

Công thức này có thể đựng nhiều biến. Ví dụ: chúng ta có thể tinh chỉnh nó để đếm gồm bao nhiêu thành phầm được cài đặt hơn một tuần trước và không giao hàng:

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

Như vậy với bài viết này, ingamemobi.com.hocexecl.online đã chia sẻ cũng như giải đáp cho các bạn một cách thực hiện công thức CountIFS và CountIF vào Excel một cách chi tiết nhất, giả dụ có ngẫu nhiên thắc mắc nào chúng ta hãy phản hồi ngay ở mặt dưới bài viết để shop chúng tôi có thể giải quyết mọi thắc mắc của người sử dụng một phương pháp sớm nhất.