COUNTIF() Excel: Đếm ô dựa trên điều kiện

Mọi cách khác nhau để dùng COUNTIF() trong Excel

Giờ bạn đã biết cách hàm COUNTIF() hoạt động trong Excel, hãy xem thêm một số ví dụ về nơi chúng ta có thể dùng nó.

Đếm các ô chứa văn bản

COUNTIF() có thể đếm số lần một văn bản cụ thể xuất hiện trong dữ liệu. Ví dụ, tôi có danh sách trái cây ở A2:A10 và muốn biết Táo xuất hiện bao nhiêu lần. Với việc này, tôi nhập:

=COUNTIF(A2:A10, "Apple")

Công thức này sẽ đếm tất cả các ô trong phạm vi đó khớp chính xác Apple.

Đếm ô có văn bản bằng COUNTIF(). Hình: Tác giả.

Đếm giá trị số

Chúng ta cũng có thể đếm các số lớn hơn, nhỏ hơn hoặc bằng một giá trị cụ thể. Ví dụ, tôi có một tập dữ liệu và giờ có thể tìm ra nhiều điều từ đó với công thức COUNTIF().  

• Để đếm có bao nhiêu loại trái cây có số lượng lớn hơn 500: =COUNTIF(B2:B10,">500")

• Để đếm có bao nhiêu loại trái cây có số lượng khác 500: =COUNTIF(B2:B10, "<>500")

• Để tìm có bao nhiêu loại trái cây có số lượng đúng bằng 500: =COUNTIF(B2:B10, “500”)

• Để đếm có bao nhiêu loại trái cây có số lượng dưới 500: =COUNTIF(B2:B10,"<500")

Đếm giá trị số bằng hàm COUNTIF(). Hình: Tác giả.

Dùng ký tự đại diện cho khớp một phần

Đôi khi chúng ta muốn đếm các ô chứa một phần của từ hoặc cụm từ. Khi đó, chúng ta dùng ký tự đại diện. Có hai loại ký tự đại diện:  

• * khớp với mọi số lượng ký tự.

• ? chỉ khớp với một ký tự.

Ví dụ, tôi có một tập dữ liệu trái cây và tôi dùng hai công thức khác nhau để cho bạn thấy COUNTIF() xử lý các trường hợp ký tự đại diện như thế nào. Để đếm tất cả các loại trái cây chứa chữ n ở bất kỳ đâu trong danh sách trái cây, tôi nhập công thức sau:

=COUNTIF(A2:A10, "*n*")

Công thức này dùng ký tự đại diện dấu sao *, đại diện cho bất kỳ số lượng ký tự nào (kể cả 0). Bằng cách gõ "*n*", Excel tìm các ô mà "n" xuất hiện ở bất kỳ vị trí nào trong văn bản — đầu, giữa hoặc cuối. Công thức đếm tất cả các ô khớp trong phạm vi A2:A10 và trả về tổng số trái cây chứa chữ cái "n".

Để đếm tất cả các loại trái bốn chữ cái bắt đầu bằng K, tôi gõ:

=COUNTIF(A2:A10,"K???")

Công thức này dùng ký tự đại diện ?, đại diện cho chính xác một ký tự. Bằng cách gõ "K???", Excel đếm tất cả các ô trong phạm vi A2:A10 bắt đầu bằng chữ cái K và có đúng bốn chữ cái rồi hiển thị kết quả cuối cùng. 

Dùng ký tự đại diện trong hàm COUNTIF(). Hình: Tác giả.

Áp dụng điều kiện từ ô khác

Thay vì viết cứng điều kiện, chúng ta có thể dùng tham chiếu ô. Cách này làm công thức linh hoạt hơn vì nó tự động cập nhật khi giá trị ô thay đổi.

Nếu tôi muốn biết có bao nhiêu loại trái cây có số lượng lớn hơn 300, tôi gõ điều kiện vào ô D2 và sau đó dùng tham chiếu ô này trong công thức như sau:

=COUNTIF(B2:B11, D2)

Hoặc chúng ta có thể viết toán tử logic trong dấu ngoặc kép bên trong công thức và dùng & để tham chiếu ô D3 như sau:

=COUNTIF(B2:B11, ">"&D3)

Đếm ô bằng tham chiếu ô trong hàm COUNTIF(). Hình: Tác giả.

Đếm ô trống và không trống

Dữ liệu thiếu cũng quan trọng như dữ liệu bạn có. Và COUNTIF() giúp chúng ta phát hiện các khoảng trống đó. Giả sử tôi đang quản lý tồn kho và muốn biết có bao nhiêu loại trái cây đã hết hàng (ô trống). Với việc này, tôi tạo công thức sau:

=COUNTIF(B2:B11,"")

Và để đếm có bao nhiêu loại trái cây còn hàng (ô không trống), tôi dùng công thức này:

=COUNTIF(B2:B11, "<>")

Tuy nhiên, nếu ô của bạn chứa bất kỳ văn bản vô hình nào như khoảng trắng hay ký tự đặc biệt, COUNTIF() sẽ tính chúng là ô không trống.

Đếm ô trống và không trống bằng hàm COUNTIF(). Hình: Tác giả.

Đếm ngày dựa trên các điều kiện khác nhau   

Chúng ta cũng có thể đếm ngày theo các điều kiện khác nhau. Giả sử chúng ta muốn đếm có bao nhiêu nhân viên được tuyển vào ngày trong ô B11 (1 Tháng 3 2024). Vậy công thức sẽ là:

=COUNTIF(B2:B9, B11)

Và để kiểm tra ai được tuyển vào hoặc trước ngày trong ô B11, tôi dùng công thức này:

=COUNTIF(B2:B9, ">="&B11)

Đếm chênh lệch ngày bằng hàm COUNTIF(). Hình: Tác giả.

COUNTIF() với nhiều tiêu chí 

Chúng ta cũng có thể dùng COUNTIF() với nhiều tiêu chí. Nếu tôi muốn đếm có bao nhiêu ngày rơi vào khoảng từ 1 Tháng 1, 2024 đến 30 Tháng 1, 2024, tôi gõ:

=COUNTIF(B2:B9, ">="&B12) - COUNTIF(B2:B9, ">"&B13)

Ngoài ra, bạn có thể dùng hàm COUNTIFS() cho nhiều tiêu chí như sau:

=COUNTIFS(B2:B9,">="&B12,B2:B9,"<="&B13)

Cả hai sẽ cho cùng kết quả. 

Dùng COUNTIF() cho hai điều kiện. Hình: Tác giả.

COUNTIF() so với COUNTIFS() trong Excel

Hàm COUNTIF() của Excel đếm ô dựa trên một tiêu chí duy nhất, như chúng ta đã thấy ở các ví dụ phía trên, và chúng ta thậm chí có thể lọc nhiều điều kiện bằng cách dùng hơn một COUNTIF(), như ở ví dụ cuối.

Đây cũng là lúc nói rằng, nếu dữ liệu của bạn cần nhiều tiêu chí, Excel cung cấp một hàm mở rộng và mới hơn là COUNTIFS(). Trong khi COUNTIFS() hoạt động giống hệt COUNTIF() khi chỉ có một điều kiện, nó còn cho phép bạn áp dụng nhiều điều kiện.

Mặc dù COUNTIFS() mới hơn và mạnh hơn với các điều kiện AND, COUNTIF() vẫn có chỗ đứng trong Excel. Nó thường tạo ra công thức dễ đọc hơn cho các điều kiện đơn giản, giúp bảng tính của bạn dễ hiểu và dễ bảo trì. Quan trọng hơn, không giống COUNTIFS(), COUNTIF() xử lý các điều kiện OR, khi bạn muốn đếm các ô thỏa mãn bất kỳ một trong số nhiều tiêu chí.

Hãy hiểu điều này qua một ví dụ đơn giản: 

=COUNTIF(B2:B11, "100") + COUNTIF(B2:B11, "900")

Công thức này sẽ đếm các ô có giá trị là 100 hoặc 900. Vì không có 900 trong các ô và chỉ có một 100, nó sẽ hiển thị 1 làm kết quả.

Áp dụng logic OR bằng COUNTIF(). Hình: Tác giả. 

Giả sử, tôi cũng cần biết bao nhiêu lần một hàng có giá trị 300 và kiwi cùng xuất hiện. Với việc này, tôi có thể dùng COUNTIFS():

=COUNTIFS(B1:B11, "300", A1:A11, "kiwi")

Công thức sẽ đếm các hàng thỏa mãn đồng thời cả hai điều kiện. Tuy nhiên, nó trả về 0 vì không điều kiện nào được thỏa mãn. 

Áp dụng logic AND bằng COUNTIFS(). Hình: Tác giả. 

Sự khác biệt này hữu ích để biết, đó là lý do chúng tôi đưa vào cheat sheet công thức Excel. Và nếu bạn muốn luôn cập nhật những phát triển mới nhất, tôi khuyên bạn nên học lộ trình kỹ năng Excel Fundamentals.

Lỗi thường gặp và cách khắc phục

Ngay cả người dùng Excel giỏi nhất đôi khi cũng gặp vấn đề với COUNTIF(). Hàm này hữu ích nhưng có vài đặc điểm dễ gây nhầm lẫn. Dưới đây là những vấn đề phổ biến nhất và cách giải quyết nhanh.

Đếm sai với chuỗi văn bản dài

COUNTIF() không hỗ trợ chuỗi văn bản dài hơn 255 ký tự. Nếu bạn làm việc với đoạn văn bản dài và nhận kết quả bất ngờ, hãy thử dùng CONCATENATE() hoặc toán tử & để tách nhỏ và ghép tiêu chí lại ở dạng dễ xử lý: 

=COUNTIF(range, CONCATENATE("First part of text", "Second part of text"))

Hoặc

=COUNTIF(range, "First part of text" & "Second part of text")

Lỗi VALUE! trong COUNTIF()

Lỗi #VALUE! trong COUNTIF() thường xảy ra khi công thức tham chiếu tới một sổ làm việc đang đóng. Điều này xảy ra vì Excel không cho phép COUNTIF() đếm dữ liệu từ tệp ngoài trừ khi tệp đó đang mở. Để khắc phục, hãy đảm bảo sổ làm việc được tham chiếu đang mở trước khi chạy công thức. Lỗi sẽ biến mất khi tệp mở và COUNTIF() sẽ hoạt động bình thường.

Dùng sai toán tử

Đôi khi chúng ta quên đặt các toán tử logic trong dấu ngoặc kép. Luôn đảm bảo các toán tử như >, < và <> nằm trong dấu ngoặc kép trong tiêu chí của bạn. Nếu không, bạn sẽ không nhận được kết quả.

 ví dụ, công thức dưới đây sẽ báo lỗi:

=COUNTIF(B2:B10,>500)

Để tránh điều này, hãy đảm bảo toán tử logic được đặt trong dấu ngoặc kép. 

=COUNTIF(B2:B10,">500")

Thực hành tốt nhất với COUNTIF() trong Excel

Dưới đây là một số thực hành quan trọng khi bạn làm việc với COUNTIF() trong Excel. 

• Dùng phạm vi có tên: Thay vì chọn một phạm vi như A2:A10 trong công thức, hãy đặt tên tùy chỉnh cho phạm vi để công thức dễ hiểu hơn. Ví dụ, để đếm số lần Banana xuất hiện trong danh sách, ta dùng =COUNTIF(A2:A10, "Banana"). Nhưng nếu tôi đặt tên phạm vi là Items và dùng tham chiếu ô cho điều kiện, tôi có thể cập nhật công thức như sau: =COUNTIF(Items, C2). Cách này giúp công thức rõ ràng và dễ hiểu hơn. 

• Dùng COUNTIFS() cho nhiều điều kiện: Đôi khi, chúng ta phải đếm dữ liệu dựa trên nhiều điều kiện. Trong trường hợp như vậy, thay vì dùng nhiều công thức COUNTIF(), hãy dùng COUNTIFS() vì đây là lựa chọn tốt nhất để áp dụng nhiều bộ lọc.

COUNTIF() và các hàm liên quan

Ngoài COUNTIF() và COUNTIFS(), chúng ta có nhiều hàm Excel khác để đếm ô. Tuy nhiên, mỗi hàm có mục đích riêng. Vậy nên, hãy xem kỹ hơn các hàm này:

Cũng như nhiều hàm Excel khác, COUNTIF() là một trong những cách hữu ích nhất để đếm và lọc dữ liệu. Thay vì kiểm tra thủ công danh sách, bạn có thể dùng hàm này để tìm thứ mình cần ngay lập tức. Và hay nhất là nó hoạt động với văn bản, số, ô trống và mẫu, giúp việc phân tích dữ liệu trở nên dễ dàng hơn. 

Nếu bạn muốn học thêm, hãy bắt đầu với khóa Introduction to Excel để nắm các kiến thức cơ bản. Để có nền tảng vững chắc, lộ trình kỹ năng Excel Fundamentals sẽ đưa bạn qua các khái niệm cốt lõi từng bước. Cuối cùng, nếu bạn làm việc với dữ liệu tài chính, khóa Financial Modeling in Excel sẽ hướng dẫn bạn xây dựng các mô hình và dự báo tài chính chuyên nghiệp.