Cách dùng COUNTIF trong Excel – Đếm nếu không phải là ô trống, đếm giá trị lớn hơn, đếm giá trị trùng lặp hoặc duy nhất

Microsoft Excel cung cấp rất nhiều hàm nhằm mục đích đếm những loại ô khác nhau, chẳng hạn như ô trống hoặc ô không trống, số, ngày tháng hoặc giá trị chữ, chứa các từ hoặc ký tự cụ thể, v.v …

Trong bài này, Học Excel Online sẽ tập trung vào hàm COUNTIF được dùng để đếm các ô với điều kiện chỉ định. Trước tiên, chúng ta sẽ trình bày ngắn gọn về cú pháp và cách sử dụng chung, và sau đó là một số ví dụ và cảnh báo về những điều có thể xảy ra khi sử dụng hàm này với nhiều điều kiện và các ô đặc thù.

Hàm COUNTIF – cú pháp và cách sử dụng:

Hàm COUNTIF được sử dụng để đếm các ô trong một phạm vi quy định đáp ứng một tiêu chí, hoặc điều kiện nhất định.

Ví dụ: bạn có thể viết công thức COUNTIF để tìm ra có bao nhiêu ô trong bảng tính của bạn chứa số lớn hơn hoặc nhỏ hơn số bạn chỉ định. Hoặc để đếm các ô với một từ cụ thể hoặc bắt đầu với một (các) chữ cái cụ thể.

Cú pháp của hàm COUNTIF rất đơn giản:

COUNTIF(range, criteria)

Như bạn thấy, chỉ có 2 đối số, cả hai đều được yêu cầu:

Range – xác định một hoặc nhiều ô để đếm. Bạn đặt phạm vi trong công thức như bạn thường làm trong Excel, ví dụ: A1: A20.

Criteria – xác định điều kiện đếm ô. Nó có thể là một số, chuỗi văn bản, ô tham chiếu hoặc biểu thức. Ví dụ: bạn có thể sử dụng các tiêu chí như sau: “10”, A2, “> = 10”, “từ gì đó”.

Và đây là ví dụ đơn giản nhất về hàm COUNTIF. Những gì bạn thấy trong hình dưới là danh sách những tay vợt giỏi nhất trong 14 năm qua. Công thức = COUNTIF (C2: C15, “Roger Federer”) tính số lần Roger Federer có tên trong danh sách:

Chú ý: Điều kiện không phân biệt chữ hoa chữ thường, có nghĩa là nếu bạn nhập “roger federer” làm điều kiện trong công thức trên, thì kết quả tương tự.

Ví dụ về hàm COUNTIF

Như bạn đã thấy, cú pháp của hàm COUNTIF rất đơn giản. Tuy nhiên, nó cho phép nhiều biến thể của điều kiện, bao gồm các ký tự đại diện, giá trị của ô khác, và thậm chí các hàm Excel khác. Tính đa dạng này làm cho hàm COUNTIF thực sự mạnh mẽ và phù hợp với nhiều nhiệm vụ, như bạn sẽ thấy trong các ví dụ tiếp theo.

Xem thêm: Học Word Excel cơ bản thành thạo

Hàm COUNTIF cho văn bản và số (chính xác tuyệt đối)

Trên thực tế, chúng ta đã thảo luận về hàm COUNTIF đếm các giá trị chữ khớp với một điều kiện chính xác tuyệt đối vừa rồi. Hãy để tôi nhắc lại công thức của ô có chứa một chuỗi văn bản: = COUNTIF (C2: C15, “Roger Federer”). Vì vậy, bạn nhập:

• Vùng chọn đếm là tham số đầu tiên;
• Dấu phẩy như dấu phân cách;
• Một từ hoặc một số từ trong dấu ngoặc kép làm điều kiện.

Thay vì nhập lại từ, bạn có thể sử dụng tham chiếu đến bất kỳ ô nào chứa từ hoặc các từ đó và nhận được kết quả tương tự, ví dụ: = COUNTIF (C1: C9, C7).

Hàm COUNTIF dùng tương tự cho số. Như bạn thấy trong hình dưới đây, công thức = COUNTIF (D2: D9,5) đếm các ô có giá trị 5 ở Cột D.

Hc Excel nâng cao

Hàm COUNTIF với ký tự đại diện (khớp một phần)

Trong trường hợp dữ liệu Excel của bạn bao gồm một vài biến thể của (các) từ khoá mà bạn muốn tính, thì bạn có thể sử dụng ký tự đại diện để đếm tất cả các ô chứa một từ, cụm từ hoặc chữ cái nhất định như một phần của nội dung ô.

Giả sử bạn có một danh sách các nhiệm vụ được giao cho những người khác nhau và bạn muốn biết số nhiệm vụ được giao cho Danny Brown. Bởi vì tên của Danny được viết bằng nhiều cách khác nhau, ta nhập “* Brown *” là điều kiện = COUNTIF (D2: D10, “*Brown *”).

Dấu hoa thị (*) được sử dụng thay thế các giá trị đầu và cuối, như được minh họa trong ví dụ trên. Nếu bạn cần phải khớp từng ký tự đơn, hãy nhập một dấu hỏi (?), như được trình bày dưới đây.

Tự động hóa Excel bằng VBA cho người mới

Đếm các ô bắt đầu hoặc kết thúc bằng các ký tự nhất định

Bạn có thể sử dụng ký tự đại diện, dấu sao (*) hoặc dấu chấm hỏi (?), với tùy thuộc vào kết quả mà bạn muốn.

Nếu bạn muốn biết số ô bắt đầu hoặc kết thúc bằng văn bản nhất định bất kể có bao nhiêu ký tự khác trong ô, hãy sử dụng các công thức sau:

= COUNTIF (C2: C10, “Mr*”) – tính các ô bắt đầu bằng “Mr”.

= COUNTIF (C2: C10, “* ed”) – tính các ô kết thúc bằng chữ “ed”.

Hình ảnh dưới đây minh họa cho công thức thứ hai:

Nếu bạn đang tìm kiếm một số ô bắt đầu hoặc kết thúc bằng các ký tự nhất định và chứa một số ký tự nhất định, bạn sử dụng hàm COUNTIF với dấu hỏi (?) tại điều kiện:

= COUNTIF (D2: D9, “??own”) – đếm số ô kết thúc bằng chữ “own” và có chính xác 5 ký tự trong các ô từ D2 đến D9, bao gồm cả khoảng cách.

= COUNTIF (D2: D9, “Mr ??????”) – đếm số ô bắt đầu bằng chữ “Mr” và có chính xác 8 ký tự trong các ô từ D2 đến D9, bao gồm khoảng cách.

Mẹo. Để tìm số ô chứa dấu hỏi “?” hoặc dấu hoa thị “*”, hãy gõ dấu ngã (~) trước dấu ? hoặc * trong công thức. Ví dụ, = COUNTIF (D2: D9, “* ~? *”) để đếm tất cả các ô có chứa dấu chấm hỏi trong dải D2: D9.

Hàm COUNTIF cho ô trống và không trống

Các ví dụ sau thể hiện cách bạn có thể sử dụng hàm COUNTIF trong Excel để đếm số ô trống hoặc không trống trong một phạm vi được chỉ định.

Học VBA cơ bản

COUNTIF ô không trống:

Trong một số hướng dẫn hàm COUNTIF khác, bạn có thể thấy công thức để tính các ô không trống trong Excel như thế này:

= COUNTIF (range, “*”)

Nhưng thực tế là, công thức trên chỉ đếm các ô có chứa bất kỳ giá trị văn bản nào, có nghĩa là các ô có ngày tháng và số sẽ được coi là các ô trống và không được đếm!

Nếu bạn cần một công thức tổng quát hàm COUNTIF để đếm tất cả các ô không phải là ô trống trong một phạm vi xác định, hãy dùng:

= COUNTIF (range, “<>” & “”)

Công thức này hoạt động chính xác với tất cả các loại giá trị – văn bản, ngày và số – như bạn thấy trong hình bên dưới.

COUNTIF ô trống:

Nếu bạn muốn đếm các ô trống ở một vùng nhất định, bạn nên sử dụng một công thức có ký tự đại diện cho các giá trị văn bản hoặc một tiêu chuẩn khác (với tiêu chí “”) để đếm tất cả các ô rỗng.

Công thức tính các ô không chứa bất kỳ văn bản nào: = COUNTIF (range, “<>” & “*”)

Các điều kiện được sử dụng trong công thức trên (“<>” & “*”) có nghĩa là tìm các ô không bằng *, tức là không chứa bất kỳ văn bản nào trong phạm vi được chỉ định.

Công thức tổng quát hàm COUNTIF cho khoảng trống (tất cả các loại giá trị): = COUNTIF (range, “”)

Công thức trên đếm số, ngày và giá trị văn bản. Ví dụ, công thức = COUNTIF (C2: C11, “”) trả về số của tất cả các ô trống trong dải C2: C11.

Chú ý. Hãy lưu ý rằng Microsoft Excel cung cấp một chức năng khác để đếm các ô trống = COUNTBLANK (range). Ví dụ: các công thức dưới đây sẽ cho kết quả chính xác giống như công thức COUNTIF bạn thấy trong hình ở trên:

Đếm khoảng trống: = COUNTBLANK (C2: C11)

Đếm không khoảng cách: = ROWS (C2: C11) * COLUMNS (C2: C11) -COUNTBLANK (C2: C11)

Ngoài ra, xin lưu ý rằng cả hai = COUNTIF (range, “”) và = COUNTBLANK (range) đếm các ô có các công thức được thể hiện bằng ô trống. Nếu bạn không muốn coi các ô đó là khoảng trống, hãy sử dụng công thức này: = ROWS (C2: С11) * COLUMNS (C2: С11) -countif(C2: С11, “<>” & “”).

COUNTIF lớn hơn, nhỏ hơn hoặc bằng:

Để đếm các ô có giá trị lớn hơn, nhỏ hơn hoặc bằng số bạn chỉ định, bạn chỉ cần thêm dấu so sánh tương ứng với các điểu kiện, như thể hiện trong bảng dưới đây.

Hãy lưu ý rằng trong các công thức COUNTIF, một dấu so sánh với một số luôn luôn được đặt trong dấu ngoặc kép.

Bạn cũng có thể sử dụng tất cả các công thức trên để đếm các ô dựa trên giá trị ô khác, chỉ cần thay thế số trong các tiêu chí bằng ô tham chiếu.

Chú ý. Trong trường hợp của ô tham chiếu, bạn phải đặt các dấu so sánh trong dấu nháy kép và thêm một dấu hiệu và (&) trước ô tham chiếu. Ví dụ, đếm các giá trị lớn hơn ô D3 trong dải D2: D9, bạn dùng công thức = COUNTIF (D2: D9, “>” & D3)

Nếu bạn muốn đếm các ô chứa các so sánh như là một phần nội dung của ô, nghĩa là các ký tự “>”, “<” hoặc “=”, sau đó là một số trong các điều kiện. Điều kiện như vậy sẽ được coi như là một chuỗi văn bản chứ không phải là một biểu thức số. Ví dụ: công thức = COUNTIF (D2: D9, “*> 5 *”) sẽ tính tất cả ô trong dải D2: D9 với nội dung như “Giao hàng> 5 ngày” hoặc “> 5 có sẵn”.

Sử dụng hàm COUNTIF với ngày:

Nếu bạn muốn đếm ô với ngày tháng lớn hơn, nhỏ hơn hoặc bằng ngày bạn chỉ định hoặc ngày trong một ô khác, bạn hãy làm theo cách tương tự đã làm trước đó. Tất cả các công thức trên dung cho ngày tháng như cho các con số. Sau đây là vài ví dụ:

Ngoài những công thức phổ biến này, bạn có thể sử dụng hàm COUNTIF kết hợp với các hàm Excel ngày và giờ cụ thể như TODAY () để đếm các ô dựa trên ngày hiện tại.

Dưới đây là ví dụ về sử dụng các công thức trên dữ liệu thực (tại thời điểm 25 tháng 6 năm 2014):

Hàm COUNTIF với nhiều điều kiện:

Ví dụ 1. COUNTIF với hai điều kiện về số

Một trong những ứng dụng phổ biến nhất của hàm COUNTIF 2 điều kiện là đếm các con số trong một giới hạn cụ thể, tức là nhỏ hơn X nhưng lớn hơn Y. Ví dụ, bạn có thể sử dụng công thức sau để đếm các giá trị lớn hơn 5 nhưng dưới 15 trong khoảng B2: B9.

= COUNTIF (B2: B9, “> 5”) – COUNTIF (B2: B9, “> = 15”)

Ví dụ 2. COUNTIF với nhiều điều kiện cho giá trị văn bản 

Hãy xem một ví dụ khác của hàm COUNTIF sẽ đếm 2 giá trị văn bản khác nhau. Giả sử bạn có một danh sách mua sắm và bạn muốn biết có bao nhiêu đồ uống. Vì vậy, công thức sau được dùng:

 = COUNTIF (B2: B13, “Lemonade”) + COUNTIF (B2: B13, “* juice”)

Hãy chú ý đến ký tự đại diện (*) trong điều kiện thứ hai, nó được sử dụng để đếm tất cả các loại nước trái cây trong danh sách.

Theo cách tương tự, bạn có thể viết một công thức COUNTIF với một số điều kiện. Dưới đây là ví dụ về chức năng COUNTIF để đếm nước chanh, nước trái cây và kem:

= COUNTIF (B2: B13, “Lemonade”) + COUNTIF (B2: B13, “* juice”) + COUNTIF (B2: B13, “Ice cream”)

Sử dụng chức năng COUNTIF để tìm các giá trị lặp và giá trị duy nhất:

Một công dụng khác của hàm COUNTIF là để tìm các giá trị lặp trong một cột, giữa hai cột hoặc trong một hàng.

Ví dụ 1. Tìm và đếm các giá trị lặp trong 1 cột

Ví dụ, công thức đơn giản này = COUNTIF (B2: B10, B2)> 1 sẽ tìm các giá trị trùng lặp với ô B2, trả kết quả TRUE/FALSE về cột Duplicate trong khoảng B2: B10 trong khi một hàm khác = COUNTIF (D2: D10, TRUE) sẽ cho bạn biết có bao nhiêu lần trùng lặp:

Tìm hiểu thêm: Học excel kế toán bán hàng

Ví dụ 2. Đếm số lần lặp giữa hai cột

Nếu bạn có hai danh sách riêng biệt, ví dụ danh sách tên trong các cột B và C, và bạn muốn biết có bao nhiêu tên xuất hiện trong cả hai cột, bạn có thể sử dụng hàm COUNTIF kết hợp với chức năng SUMPRODUCT để đếm số lần lặp:

 = SUMPRODUCT ((COUNTIF (B2: B1000, C2: C1000)> 0) * (C2: C1000 <> “”))

 Thậm chí ta có thể đếm bao nhiêu tên duy nhất trong Cột C, tức là các tên không xuất hiện trong Cột B:

= SUMPRODUCT ((COUNTIF (B2: B1000, C2: C1000) = 0) * (C2: C1000 <> “”))

Ví dụ 3. Đếm giá trị trùng lặp và giá trị duy nhất trong một hàng

 Nếu bạn muốn đếm các giá trị lặp hoặc các giá trị duy nhất trong một hàng nào đó thay vì một cột, hãy sử dụng một trong các công thức dưới đây. Những công thức này có thể hữu ích, thí dụ, để phân tích lịch sử xổ số.

 Đếm số lần lặp trong một hàng:

 = SUMPRODUCT ((COUNTIF (A2: I2, A2: I2)> 1) * (A2: I2 <> “”))

 Đếm các giá trị duy nhất trong một hàng:

 = SUMPRODUCT ((COUNTIF (A2: I2, A2: I2) = 1) * (A2: I2 <> “”))

Nguồn: Ablebits, dịch và biên tập bởi Hocexcel Online.