Hàm đếm có điều kiện COUNTIF và COUNTIFS trong Excel

Hàm đếm có điều kiện COUNTIF và COUNTIFS trong excel được thực hiện như thế nào ? các điều kiện được đưa ra bằng cách nào hãy cùng công nghệ tìm hiểu chi tiết. 

Công thức và cách sử dụng hàm Countifs

Công thức hàm Countifs : = COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2]…)

Trong đó : criteria_range1  là phạm vi đầu tiên áp dụng điều kiện đầu tiên (criteria1), bắt buộc.

criteria1 ( tiêu chí 1 ) – đặ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à có thể biểu diễn là 10, “<=32”, A6, “sweets”.

[criteria_range2, criteria2]… : Là các điều kiện bổ sung, tùy chọn này có thể thêm tới 127 điều kiện.

Thực tế, bạn cũng không cần phải thuộc lòng cú pháp của hàm COUNTIFS , bởi Microsoft Excel sẽ hiển thị các đối của của hàm khi bạn bắt đầu gõ.

Lưu ý: Hàm COUNTIFS không dùng được ở các phiên bản Microsoft Office thấp hơn 2007

COUNTIFS cần lưu ý khi sử dụng hàm

• Nếu muốn đếm các ô trong một phạm vi đơn lẻ, với điều kiện duy nhất cũng như trong nhiều vùng dữ liệu có nhiều điều kiện thì bạn có thể sử dụng hàm Countifs. Trường hợp thứ hai dùng hàm Countifs là đếm những ô đáp ứng tất cả các điều kiện.
• Những vùng dữ liệu bổ sung phải có cùng cố hàng và số cột  như vùng dữ liệu đầu tiên.
• Hình thức đếm này có thể đếm những vùng dữ liệu liền kề hoặc là không liền kề.
• Trường hợp tiêu chí là một tham chiếu đến một ô trống, thì hàm COUNTIFS sẽ xử lý nó như một giá trị bằng không (0).
• Bạn có thể sử dụng các wildcard characters ( ký tự đại diện ) trong các tiêu chí – dấu hoa thị (*) và dấu chấm hỏi (?).

Sử dụng hàm đếm COUNTIFS và COUNTIF có nhiều điều kiện, nhiều tiêu chí như thế nào

Cách đếm ô tính với nhiều tiêu chí 

• Cách sử dụng công thức 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ư sau : Để kiểm tra số hàng tồn kho : Giá trị cột B lớn hơn 0 nhưng chưa bán được – giá trị cột C bằng 0

Để thực hiện cách đếm này, công thức đưa ra sẽ là

= COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”)   =>> Kết quả nhận được là 2 (“Cherries” và “Lemons”):

( Lấy vùng dữ liệu chứa số hàng tồn kho với điều kiện là “>0”, tiếp theo lấy vùng điều kiện số hàng đã bán Sold với điều kiện “=0” )

• Cách sử dụng công thức COUNTIFS với các tiêu chí giống nhau

Trường hợp bạn muốn đếm nhưng mục có tiêu chí giống nhau thì bạn vẫn cần cũng cấp những cặp criteria_range / criteria một cách riêng lẻ.

Ví dụ : Để đếm các giá trị bằng 0 ở cả hai cột B và C ta sẽ có công thức như sau  = COUNTIFS ($B$2: $B$7, “= 0”, $C$2: $C$7, “= 0”)

Khi đó hàm sẽ tìm kiếm ở cả hai vùng với hai điều kiện tương đương và cho kết quả là 1 bởi chỉ có Grapes đáp ứng đủ điều kiện có giá trị bằng “0” ở cả hai cột.

Nếu chỉ sử dụng công thức đơn giản với một criteria_range duy nhất : Theo công thức : COUNTIFS(B2: C7, “= 0”) Thì kết quả trả lại sẽ khác : Ở đây kết quả trả về là 4

Cách đếm có điều kiện với nhiều tiêu chí hàm

Như vậy việc đếm các ô tính đáp ứng được tất cả các tiêu chí chỉ định rất dễ dàng bởi hàm COUNTIFS “sinh ra” để hoạt động theo cách này.

Nhưng trường hợp bận lại cấn đếm các ô có ít nhất một trong các tiêu chí được chỉ định là TRUE nghĩa là dựa theo phép tính logic OR thì cách thích hiện sẽ được tiến hành như sau :

Có 2 cách :

• Thêm công thức COUNTIF
• Sử dụng công thức SUM COUNTIFS với hằng số mảng.

Cách 1 : Cách thêm hai hoặc nhiều công thức COUNTIF hoặc COUNITFS

Ví dụ ta có một bảng như trên. Hiện tại bạn đang muốn tính các đơn đặt hàng với trạng thái là “Cancelled” và “Pending“,  Khi đó bạn chỉ có thể viết 2 công thức COUNTIF bình thường sau đó nhận kết quả : Công thức được thực hiện sẽ là :  =COUNTIF($C$2:$C$11,”Cancelled”)+COUNTIF($C$2:$C$11,”Pending”)

Nếu như bạn muốn sử dụng hàm đếm có điều kiện với nhiều hơn một điều kiện thì sử dụng COUNTIFS thay vì COUNTIF.

Ví dụ cụ thể với bảng trên, để đếm số đơn đặt hàng “Cancelled”  và “Pending” đối với “Apples”, thì ta sẽ xử 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ách 2: Công thức SUM COUNTIFS với hằng mảng

Nếu như bạn muốn thiết lập nhiều tiêu chí, thì cách trên lại không phải cách tối ưu bởi vì công thức có vẻ dài dòng. Để thực hiện các phép tính tương tự trong một công thức nhỏ gọn hơn, thì cách đơn giản là liệt kê tất cả những tiêu chí của bạn trong mảng và cung cấp mảng đó cho criteria của hàm COUNTIFS. Để đếm được tổng ta có thể kết hợp các hàm COUNTIFS bên trong hàm SUM như sau:

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

Trong bảng mẫu ở trên, nếu bạn muốn đếm các đơn đặt hàng có trạng thái “Cancelled“, “Pending” hoặc “In transit“, Công thức thực hiện như sau :

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

Tương tự với cách làm này, bạn có thể đếm các ô dựa trên hai hoặc nhiều hơn các cặp criteria_range/criteria.

Để biết số lượng đơn đặt hàng “Apples” trong trạng thái “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”}))

Xem thêm: Cách dùng hàm INDIRECT trong Excel tham chiếu linh hoạt

Xem thêm: Cách bỏ dấu chấm trong excel mà không phải ai cũng biết

Trên đây là những cách sử dụng hàm đếm có điều kiện trong Excel cơ bản. Chúc các bạn thành ông.