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

24-06-20

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 xosothantai 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ảnmộ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”.

Hàm có điều kiện trong Excel

Hàm có điều kiện trong Excel

[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 – NHỮNG ĐIỀU CẦN LƯU Ý:

  • 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 COUNTIFS và COUNTIF với 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

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

Để 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í 

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”}))

Cách đếm số trong một khoảng cụ thể

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

Cách đếm trong một khoảng cụ thể

Cách đếm trong một khoảng cụ thể

Để đếm được có bao nhiêu số giữa 5 và 10 ( không bao gồm 5 và 10 ) có trong các ô C2 đến C10. Ta sẽ sử dụng công thức sau

=COUNTIFS(C2:C10,”>5″,C2:C10,”<10″)

Trường hợp đếm cả hai số nút, tức là đếm cả 5 và 10 thì ta có thể sử dụng dấu ” >=” để làm điều kiện :

=COUNTIFS(B2:B10,”>=5″,B2:B10,”<=10″)

Cách thực hiện thứ 2  Công thức COUNTIF để đếm số giữa X và Y

Chúng ta có thể sử dụng công thức COUNTIF lồng ghép với một hàm khác khác để có kết quả tương tự. Công thức đầu tiên đếm những số lớn hơn giá trị giới hạn dưới (ví dụ là 5). Công thức thứ hai loại bỏ những số lớn hơn giá trị giới hạn trên (trong trường hợp này là 10). Kết quả bạn muốn tìm nằm trong khoảng hai giá trị nhận được trên.

=COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”=10″) – Đây là cách đếm loại trừ, ta sẽ đếm xem có bao nhiêu ô có giá trị lớn hơn 5, sau đó laijd dếm xem có bao nhiêu ô trong vùng dữ liệu có giá trị lớn hơn 10 ở trong vùng dự liệu C2:C10.

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – Còn đây là công thức để đếm xem có bao nhiêu số trong đoạn từ 5 đến 10 thuộc vùng dữ iệu C2:C10, bao gồm cả 5 và 10.

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.

Mơ thấy bọ ngựa đánh con đề gì vào bờ an toàn nhất?

Mơ thấy bọ ngựa đánh con đề gì vào bờ an toàn nhất?

29-07-20

Mơ thấy bọ ngựa được đánh giá là một trong những giấc mơ thú vị. Nó mang đến những điềm báo cũng như con số may mắn. Giải mã giấc mơ thấy bọ ngựa.

Mơ thấy sư thầy đánh con gì, là dự cảm dữ hay lành?
Mơ thấy sư thầy đánh con gì, là dự cảm dữ hay lành?

22-04-20

Tổng hợp ý nghĩa giấc mơ thấy quả lê
Tổng hợp ý nghĩa giấc mơ thấy quả lê

21-04-20

Thuốc Remdesivir có phải là thần dược chữa virut Covid-19
Thuốc Remdesivir có phải là thần dược chữa virut Covid-19

17-04-20

Mơ thấy rắn hổ mang là điềm gì, đánh con đề nào chuẩn nhất?
Mơ thấy rắn hổ mang là điềm gì, đánh con đề nào chuẩn nhất?

17-04-20

Nhận định KuPS vs Slovan Bratislava 22h30, 17/09 – Cúp C2 châu Âu

Nhận định KuPS vs Slovan Bratislava 22h30, 17/09 – Cúp C2 châu Âu

17-09-20

Nhận định KuPS vs Slovan Bratislava 22h30 ngày 17/09, vòng sơ loại Cúp C2 châu Âu 2020/20201. Nhận định bóng đá Cúp C2 Châu Âu CHÍNH XÁC.

Nhận định Tianjin Teda vs Shijiazhuang Ever Bright, 19h00 ngày 16/9
Nhận định Tianjin Teda vs Shijiazhuang Ever Bright, 19h00 ngày 16/9

16-09-20

Nhận định Sagan Tosu vs Consadole Sapporo, 17h30 ngày 16/9
Nhận định Sagan Tosu vs Consadole Sapporo, 17h30 ngày 16/9

16-09-20

Nhận định trận đấu Hacken vs Sirius (00h00 ngày 15/9)
Nhận định trận đấu Hacken vs Sirius (00h00 ngày 15/9)

14-09-20

Nhận định trận đấu Saint Etienne vs Strasbourg (2h00 ngày 13/9)
Nhận định trận đấu Saint Etienne vs Strasbourg (2h00 ngày 13/9)

12-09-20