Hướng dẫn cách dùng hàm đếm có điều kiện COUNTIFS trong Excel

Hướng dẫn cách dùng hàm đếm có điều kiện COUNTIFS trong Excel

Mục lục

Trong tất cả các hàm Excel, COUNTIFS và COUNTIF có lẽ thường dễ bị nhầm lẫn nhất vì chúng trông rất giống nhau và cả hai đều được dùng để đếm các ô dựa trên các tiêu chí đã chỉ định.

Sự khác biệt là COUNTIF được thiết kế để đếm các ô có một điều kiện trong một phạm vi, trong khi COUNTIFS có thể đánh giá các tiêu chí khác nhau trong cùng hoặc trong các phạm vi khác nhau. Mục đích của hướng dẫn này là thể hiện các cách tiếp cận khác nhau và giúp bạn chọn công thức hiệu quả nhất cho từng nhiệm vụ cụ thể.

             >>    Hàm Count và cách dùng hàm Count để đếm số lượng ô trong excel
             >>    Hàm Counta và cách dùng hàm counta để đếm ô trong excel

             >>    Chi tiết cách sử dụng hàm countblank trong Excel
             >>    Hướng dẫn chi tiết cách sử dụng hàm Dcounta trong excel

1. Hàm COUNTIFS của Excel - cú pháp và cách sử dụng

Như đã đề cập, hàm COUNTIFS của Excel được dùng để đếm các ô trên nhiều phạm vi dựa trên một hoặc một số điều kiện. Hàm COUNTIFS có sẵn trong Excel 2016, 2013, Excel 2010 và Excel 2007, vì vậy bạn có thể sử dụng các ví dụ dưới đây trong bất kỳ phiên bản Excel nào.

Cú pháp: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

Trong đó: 

  • criteria_range1: Vùng chứa điều kiện thứ nhất
  • criteria1    : Điều kiện thứ nhất( thuộc trong vùng điều kiện thứ nhất)
  • criteria_range2: Vùng chứa điều kiện thứ hai( yêu cầu vùng criteria_range2 phải có độ lớn bằng nhau)
  • criteria2, ...    Điều kiện thứ hai (thuộc trong vùng điều kiện thứ nhất). Bạn có thể chỉ định tối đa 127 cặp phạm vi / tiêu chí trong công thức của mình.

Những điều ai cũng nên biết về hàm đếm có nhiều điều kiện

Thực tế, bạn không cần phải nhớ cú pháp của hàm COUNTIFS. Microsoft Excel sẽ hiển thị các đối số của hàm ngay khi bạn bắt đầu nhập; đối số bạn đang nhập vào lúc này được tô đậm.

LƯU Ý: 
Bạn có thể sử dụng hàm COUNTIFS trong Excel để đếm các ô trong một phạm vi với một điều kiện duy nhất cũng như trong nhiều phạm vi với nhiều điều kiện. Nếu sau này, chỉ những ô đáp ứng  tất cả các điều kiện được chỉ định mới  được tính.
Mỗi phạm vi bổ sung phải có  cùng số hàng và cột  như phạm vi đầu tiên.Cả phạm vi tiếp giáp và không liền kề đều được cho phép.
Nếu tiêu chí là một tham chiếu đến một  ô trống , hàm COUNTIFS coi nó là giá trị 0 (0).
Bạn có thể sử dụng các  ký tự đại diện  trong tiêu chí - dấu hoa thị (*) và dấu hỏi (?). 


2 Cách sử dụng COUNTIFS  với nhiều tiêu chí

Dưới đây bạn sẽ tìm thấy một số ví dụ về công thức minh họa cách sử dụng các hàm COUNTIFS trong Excel để đánh giá nhiều điều kiện.

Cách đếm các ô có nhiều tiêu chí

  • Công thức 1. Công thức COUNTIFS với nhiều tiêu chí

Giả sử bạn có một danh sách sản phẩm như hiển thị trong ảnh chụp màn hình bên dưới. Bạn muốn có được một số lượng các mặt hàng trong kho (giá trị trong cột B lớn hơn 0) nhưng chưa được bán (giá trị là cột C bằng 0).
Nhiệm vụ có thể được thực hiện bằng cách sử dụng công thức này:
=COUNTIFS(B2:B7,">0", C2:C7,"=0")
Và số lượng là 2 ("Anh đào " và " Chanh "):

Những điều ai cũng nên biết về hàm đếm có nhiều điều kiện

  • Công thức 2. Công thức COUNTIFS có cùng tiêu chí

Khi bạn muốn đếm các mục có tiêu chí giống hệt nhau, bạn vẫn cần cung cấp từng cặp tiêu chí_range / tiêu chí .
Ví dụ: đây là công thức đúng để đếm các mục có 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 vì chỉ " Nho " có giá trị "0" trong cả hai cột

Những điều ai cũng nên biết về hàm đếm có nhiều điều kiện

Cách đếm ô có nhiều tiêu chí (logic OR)

Như bạn đã thấy trong các ví dụ trên, việc đếm các ô đáp ứng tất cả các tiêu chí đã chỉ định là dễ dàng vì hàm COUNTIFS được thiết kế để hoạt động theo cách này.
Nhưng nếu bạn muốn đếm các ô mà ít nhất một trong các điều kiện được chỉ định là TRUE , tức là dựa trên logic OR thì sao? Nhìn chung, có hai cách để thực hiện việc này - bằng cách thêm một số công thức COUNTIFS hoặc sử dụng công thức SUM COUNTIFS.

  • Công thức 1. Thêm hai hoặc nhiều công thức trong COUNITFS

Ví dụ: để có được số lượng đơn đặt hàng " Đã hủy " và " Đang chờ xử lý" cho " Táo ", hãy sử dụng công thức sau:
=COUNTIFS($A$2:$A$11, "Táo", $C$2:$C$11,"Đã hủy") + COUNTIFS($A$2:$A$11, "Táo", $C$2:$C$11,"Đang chờ xử lý")

Những điều ai cũng nên biết về hàm đếm có nhiều điều kiện

  • Công thức 2. SUMTIFS với hằng số mảng

Trong các tình huống khi bạn phải đánh giá rất nhiều tiêu chí, cách trên không phải là cách tốt nhất vì công thức của bạn sẽ quá dài.
Để 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, hãy liệt kê tất cả các tiêu chí của bạn trong một hằng số mảng và cung cấp mảng đó cho đối số tiêu chí của hàm COUNTIFS. 

Ta sẽ dùng công thức sau:

  • =SUM(COUNTIFS(range,{"criteria1","criteria2","criteria3",…}))

Trong bảng dưới đây, để đếm ‘Táo” có  trạng thái " Đã hủy " hoặc " Đang chờ xử lý " hoặc " Nhập kho ", công thức sẽ như sau:

  • =SUM(COUNTIFS($A$2:$A$11,"Táo",$C$2:$C$11,{"Đã hủy","Đang chờ xử lý","Nhập kho"}))

Những điều ai cũng nên biết về hàm đếm có nhiều điều kiện

Cách đếm số giữa 2 số được chỉ định

Công thức . COUNTIFS để đếm các ô giữa hai số
Để tìm hiểu có bao nhiêu số từ 5 đến 10 (không bao gồm 5 và 10) được chứa trong các ô từ C2 đến C10, hãy sử dụng công thức này:
= COUNTIFS (C2: C10, "> 5", C2: C10, "<10")

Những điều ai cũng nên biết về hàm đếm có nhiều điều kiện

Để đếm các số  5 và 10 trong số đếm, hãy sử dụng công thức "lớn hơn hoặc bằng" và "nhỏ hơn hoặc bằng":
= COUNTIFS (B2: B10, "> = 5", B2: B10, "<= 10").

Trên đây là các cách thông dụng nhất để  bạn đếm các ô có nhiều tiêu chí trong Excel. Để hiểu rõ hơn về hệ thống hàm trong excel, bạn có thể tham khảo ngay khóa học TRỞ THÀNH CAO THỦ EXCEL TRONG 10 GIỜ tại Unica. Khóa học sẽ giúp bạn nắm vững các hàm excel thông dụng và đặc biệt nhất. Từ đó, dễ dàng thống kê và phân tích số liệu một cách hiệu quả, cải thiện tối đa hiệu suất công việc.

Cảm ơn các bạn đã đọc bài và chúc thành công!


Tags: Excel