Hướng dẫn dùng hàm countifs năm 2024

Trong tất cả các hàm Excel, hàm đếm có điều kiện COUNTIFS và COUNTIF là hai hàm tính thông dụng nhất. Sự khác biệt tiêu biểu nhất giữa hai hàm này 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 điều kiện khác nhau trong cùng hoặc trong các phạm vi khác nhau. Để giúp bạn đọc có thể hiểu rõ hơn về hàm đếm có nhiều điều kiện trong excel, mời bạn tham khảo nội dung bài viết sau đây để có câu trả lời nhé.

1. Công thức và cách dùng hàm đếm có nhiều điều kiện trong excel COUNTIFS

Như đã đề cập, hàm COUNTIFS trong Excel được dùng để đếm số lần xuất hiện giá trị trong Excel 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ông thức countif: =COUNTIFS[criteria_range1, criteria1, [criteria_range2, criteria2],…]

Trong đó:

- criteria_range1: Vùng chứa điều kiện thứ nhất, đây là phần bắt buộc phải có

- criteria1: Điều kiện thứ nhất [thuộc trong vùng điều kiện thứ nhất] gồm có các dạng số, ô tham chiếu, chuỗi văn bản, hàm Excel hoặc vùng Excel khác.

- 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 / điều kiện trong công thức của mình.

- Tương tự như vậy với các điều kiện khác cũng sẽ áp dụng như vậy.

Công thức hàm đếm có nhiều điều kiện trong excel

Thực tế, bạn không cần phải nhớ cú pháp của hàm đếm có điều kiện 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.

Bạn chỉ cần nhớ bạn muốn sử dụng hàm COUNTIFS trong Excel là được.

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.

- Criteria1 bạn có thể biểu diễn chúng giống như các ví dụ: 10, "0", C2:C7,"=0"]

Và số lượng là 2 ["Anh đào " và " Chanh "]:

Công thức hàm Countifs

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ó điều kiện giống hệt nhau, bạn vẫn cần cung cấp từng cặp điều kiện_range / điều kiện .

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

Công thức hàm COUNTIFS có cùng tiêu chí

2.2. Cách đếm ô có nhiều điều kiện khác nhau [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 điều kiện đã chỉ định là dễ dàng vì hàm COUNTIF nhiều điều kiện đượ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 COUNTIFS

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ý"]

Công thức đếm ô tính có nhiều điều kiện

Công thức 2. SUMIFS 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 điều kiện thì 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 điều kiện của bạn trong một hằng số mảng và cung cấp mảng đó cho đối số điều kiện của hàm đếm có điều kiện 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"}]]

SUMIFS với hằng số mảng

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

Ngoài những cách dùng hàm đếm có nhiều điều kiện trong excel như bên trên đã chia sẻ, bạn cũng có thể sử dụng hàm COUNTIFS để đếm số trong khoảng thời gian cụ thể. Đối với vấn đề này thì sẽ có 2 công thức cơ bản nhất, cụ thể đó là:

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

Công thức COUNTIFS 2 điều kiện sử dụng để đế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, " = 5", B2: B10, " Nhập công thức =SUM[COUNTIF[A3:A11, "Red"], COUNTIF[B3:B11, "Big"]] => Enter. Khi này, excel đã đếm cho bạn số lượng táo cỡ lớn hoặc táo có màu đỏ.

Kết quả đếm cuối cùng

5. Cách sửa các lỗi thường gặp khi dùng hàm đếm có điều kiện trong excel

Trong quá trình sử dụng hàm đếm có điều kiện lớn hơn và nhỏ hơn người dùng rất hay gặp lỗi. Sau đây là một số lỗi thường gặp cùng cách khắc phục cho bạn tham khảo, lưu lại ngay nhé.

5.1. COUNTIFS không hoạt động khi đếm các giá trị text

Khi đếm các giá trị text, để hoạt động chúng phải được điền vào trong dấu ngoặc kép. Nếu không được chèn, hàm COUNTIFS sẽ không thể đếm được, khi không đếm được chuỗi văn bản sẽ trả về giá trị 0.

Ví dụ dưới đây text không đặt trong dấu ngoặc kép nên kết quả trả về bằng 0

Kết quả trả về bằng 0 do sai công thức

Để khắc phục lại này bạn chỉ cần nhập lại công thức có dấu ngoặc ở text là được. Công thức: =COUNTIFS[E5:E12, "Car"]

Công thức đúng khi đếm giá trị text

5.2. COUNTIFS không hoạt động vì sai tham chiếu phạm vi

Khi sử dụng hàm đếm có nhiều điều kiện trong excel bạn rất hay gặp lỗi hàm COUNTIFS không hoạt động vì sai tham chiếu phạm vi. Vì vậy khi dùng nhiều hơn một tiêu chí trong hàm COUNTIFS, phạm vi của ô tiêu chí khác phải có cùng số lượng. Nếu không hàm COUNTIFS sẽ lỗi.

Ví dụ: Bạn muốn đếm doanh số bán ô tô ở Austin. Công thức bạn nhập vào bảng đó là =COUNTIFS[E5:E12,"Car",D5:D11,"Austin"]. Quan sát kỹ công thức, bạn sẽ thấy phạm vi tiêu chí đầu tiên ở đây là E5:E12 nhưng phạm vi ở tiêu chí thứ hai là D5:D11. Khi số ô trong phạm vi cho tiêu chí không giống nhau thì kết quả sẽ trả về lỗi

VALUE!.

Công thức nhập vào bảng tiêu chí không giống nhau

Cách khắc phục đó là bạn nhập lại công thức thành =COUNTIFS[E5:E12,"Car",D5:D12,"Austin"]. Như vậy mới trả về kết quả đúng được.

5.3. COUNTIFS không hoạt động vì lỗi trong công thức

Trong quá trình làm việc với COUNTIFS nếu không chèn công thức chính thức thì chắc chắn sẽ bị lỗi hàm không hoạt động. Nếu bạn dùng toán tử học, ví dụ như: dấu lớn hơn [>], nhỏ hơn [100000"] => Enter. Khi đã nhập cả hai toán tử và tiêu chí bên trong dấu ngoặc thì bạn sẽ nhận được kết quả đúng như mong muốn.

Kết quả trả về khi nhập đúng công thức

6. Những điều cần lưu ý khi dùng hàm COUNTIFS

Dưới đây là một số điều cần lưu ý khi dùng hàm COUNTIFS, bạn hãy ghi nhớ nhé:

- Các vùng chọn bổ sung cần phải có cùng số hàng và cột với vùng criteria_range1. Nếu không, hàm COUNTIFS sẽ trả về kết quả sai.

- Điều kiện vùng chọn tham chiếu đến ô trống thì hàm COUNTIFS tự động coi giá trị là 0.

- Có thể dùng các ký tự đại diện— dấu hỏi [?] để tìm kiếm các giá trị không xác định. Ví dụ, công thức =COUNTIFS[A2:A10,"A"] sẽ đếm tất cả các ô trong phạm vi A2:A10 có chứa chữ "A" ở bất kỳ vị trí nào.

- Lưu ý rằng hàm COUNTIFS không phân biệt chữ hoa chữ thường. Ví dụ, công thức =COUNTIFS[A2:A10,"a"] sẽ đếm tất cả các ô trong phạm vi A2:A10 có chứa chữ "a" hoặc "A".

7. Kết luận

Trên đây là toàn bộ thông tin về hàm đếm có nhiều điều kiện trong excel mà Unica đã tổng hợp được. Hàm đếm có điều kiện là cách thông dụng nhất để bạn thực hiện thao tác đếm trong excel đơn giản, 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. Ngoài kiến thức về hàm đếm có điều kiện trong Excel các bạn có thể tham khảo các khóa học Excel online tại Unica với cực kỳ nhiều ưu đãi bạn nhé