Hàm COUNTIF trong Excel Hàm đếm dữ liệu có điều kiện

COUNTIF là tên một hàm Excel giúp bạn thống kê dữ liệu nhanh chóng và chính xác. Hãy cùng Thptlequydontranyenyenbai.edu.vn tìm hiểu những điều cần biết về cách sử dụng hàm COUNTIF trong Excel nhé!

Cách dùng hàm COUNTIF trong Excel

COUNTIF là một hàm Excel đếm các ô trong phạm vi đáp ứng một điều kiện. COUNTIF có thể được dùng để đếm ô chứa ngày, số và text. Tiêu chí trong hàm COUNTIF hỗ trợ các toán tử logic (>,<,<>,=) và ký tự đại diện (*,?) cho đối sánh từng phần.

• Mục đích sử dụng: Đếm ô đáp ứng tiêu chí
• Giá trị trả về: Số lượng ô đếm được
• Công thức: =COUNTIF (range, criteria). Trong đó:
  • Range – Phạm vi ô cần đếm
  • Criteria – Tiêu chí ô cần đếm
• Sử dụng hàm COUNTIF trên Excel 2003 trở lên

Lưu ý khi sử dụng hàm COUNTIF trong Excel

Như đã nói từ đầu, hàm COUNTIF trong Excel đếm số ô trong phạm vi khớp với điều kiện đưa ra. Tiêu chí bao gồm các toán tử logic và ký tự đại diện để đối sánh từng phần. Tiêu chí cũng có thể dựa trên một giá trị từ ô khác.

COUNTIF nằm trong một nhóm 8 hàm của Microsoft Excel. Nó chia tiêu chí logic thành 2 phần (phạm vi + tiêu chí). Kết quả, công thức xây dựng tiêu chí sẽ khác nhau và COUNTIF cần một phạm vi ô nên bạn không thể dùng mảng.

COUNTIF chỉ hỗ trợ một điều kiện duy nhất. Nếu cần áp dụng nhiều tiêu chí, hãy dùng hàm COUNTIFS.

Ví dụ về cách dùng hàm COUNTIF trong Excel

Ví dụ cơ bản

Trong ảnh minh họa ở đầu bài, công thức sau được dùng trong ô G5, G6 và G7:

=COUNTIF(D5:D12,">100") // đếm doanh số hơn 100
=COUNTIF(B5:B12,"jim") // đếm tên = "jim"
=COUNTIF(C5:C12,"ca") // đếm bang = "ca"

Lưu ý, COUNTIF không phần biệt chữ hoa & chữ thường, “CA” hay “ca” đều giống nhau.

Ngoặc kép (“”) trong tiêu chí

Về cơ bản, các giá trị text cần phải nằm trong dấu ngoặc kép, còn số thì không. Tuy nhiên, khi một toán tử logic bao gồm một số, số và toán tử đó phải nằm trong ngoặc kép, cụ thể như sau:

=COUNTIF(A1:A10,100) // đếm các ô bằng 100
=COUNTIF(A1:A10,">32") // đếm các ô lớn hơn 32
=COUNTIF(A1:A10,"jim") // đếm các ô bằng "jim"

Giá trị từ ô khác

Bạn có thể đưa một giá trị từ ô khác vào tiêu chí bằng phép nối. Trong ví dụ này, COUNTIF sẽ trả về số lượng các giá trị trong A1:A10, ít hơn giá trị ở ô B1. Lưu ý, dấu nhỏ hơn sẽ nằm trong trích dẫn.

=COUNTIF(A1:A10,"<"&B1) // đếm các ô nhỏ hơn B1

Không bằng

Để xây dựng tiêu chí không bằng, dùng “<>” nằm trong dấu ngoặc kép (“”). Ví dụ, công thức bên dưới sẽ đếm các ô không phải “red” trong phạm vi A1:A10:

=COUNTIF(A1:A10,"<>red") // không phải "red"

Các ô trống

COUNTIF có thể đếm các ô trống và không trống. Công thức bên dưới đếm những ô này trong phạm vi A1:A10:

=COUNTIF(A1:A10,"<>") // không trống
=COUNTIF(A1:A10,"") // trống

Ngày tháng

Cách dễ nhất để dùng COUNTIF với ngày tháng là tham chiếu tới một ngày hợp lệ trong ô khác với cùng một tham chiếu ô. Ví dụ, để đếm ô trong A1:A10, chứa ngày lớn hơn ngày ở B1, bạn có thể dùng công thức sau:

=COUNTIF(A1:A10, ">"&B1)

Cách an toàn nhất để mã hóa ngày vào COUNTIF là dùng hàm DATE. Điều này đảm bảo Excel sẽ hiểu ngày tháng đó. Để đếm ô trong A1:A10 chứa ngày trước 1/4/2020, bạn có thể dùng công thức sau:

=COUNTIF(A1:A10,"<"&DATE(2020,4,1))

Ký tự đại diện

Dấu hỏi, hoa thị và ngã có thể được dùng trong tiêu chí. Dấu hỏi đối sánh 1 ký tự, dấu hoa thị đối sánh 0 hoặc nhiều ký tự hơn (thuộc loại bất kỳ). Ví dụ, để đếm ô trong A1:A5 chứa apple, bạn có thể dùng công thức sau:

=COUNTIF(A1:A5,"*apple*")

Đếm các ô trong A1:A5 chứa 3 ký tự text bất kỳ, bạn có thể dùng:

=COUNTIF(A1:A5,"???")

Tóm lại, bạn cần nhớ những điều sau khi dùng hàm COUNTIF trong Excel:

• COUNTIF không phân biệt chữ hoa chữ thường.
• COUNTIF chỉ hỗ trợ một điều kiện.
• Chuỗi text trong tiêu chí phải nằm trong ngoặc kép (“”).
• Các tham chiếu ô trong tiêu chí không nằm trong ngoặc kép.
• Ký tự đại diện ? và * có thể dùng trong tiêu chí.
• Để đối sánh dấu hỏi hoặc hoa thị, dùng dấu ngã ở phía trước chúng. (ví dụ: ~?, ~*).
• COUNTIF cần một phạm vi.
• COUNTIF trả về kết quả sai khi đối sánh chuỗi dài hơn 255 ký tự.
• COUNTIF trả về lỗi #VALUE khi tham chiếu workbook bị đóng khác.