Microsoft Excel (Bài 74): Hàm COUNTIF – cú pháp và cách sử dụng trong Excel

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.

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.

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.

ĐẾ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.

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, “”) = 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.

Điều kiện Công thức ví dụ Diễn giải
Đếm nếu lớn hơn =COUNTIF(A2:A10,”>5″) Đếm những ô có giá trị lớn hơn 5
Đếm nếu bé hơn =COUNTIF(A2:A10,”<5″) Đếm những ô có giá trị bé hơn 5
Đếm nếu bằng =COUNTIF(A2:A10,”=5″) Đếm những ô có giá trị bằng 5
Đếm nếu khác =COUNTIF(A2:A10,”<>5″) Đếm những ô có giá trị khác 5
Đếm nếu bằng hoặc lớn hơn =COUNTIF(C2:C8,”>=5″) Đếm những ô có giá trị lớn hơn hoặc bằng 5
Đếm nếu bằng hoặc bé hơn =COUNTIF(C2:C8,”<=5″) Đếm những ô có giá trị bé hơn hoặc bằng 5

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ụ:

Điều kiện Công thức ví dụ Diễn giải
Đếm một ngày cụ thể =COUNTIF(B2:B10,”6/1/2014″) Đếm số ô trong vùng B2:B10 có ngày 6/1/2014
Đếm ngày lớn hơn hoặc bằng một ngày khác =COUNTIF(B2:B10,”>=6/1/2014″) Đếm số ô trong vùng B2:B10 có ngày lớn hơn hoặc bằng 6/1/2014
Đếm ngày lớn hơn hoặc bằng ngày trong một ô khác trừ đi x ngày =COUNTIF(B2:B10,”>=”&B2-“7”) Đếm số ô trong vùng B2:B10 có ngày lớn hơn hoặc bằng ngày trong ô B2 trừ đi 7 ngày

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.

Điều kiện Công thức ví dụ
Đếm ngày bằng với ngày hiện tại =COUNTIF(A2:A10,TODAY())
Đếm ngày nhỏ hơn ngày hiện hành, ví dụ hôm nay =COUNTIF(A2:A10,”<“&TODAY())
Đếm ngày sau ngày hiện hành, ví dụ sau hôm nay =COUNTIF(A2:A10,”>”&TODAY())
Đếm ngày sau ngày hôm nay 1 tuần =COUNTIF(A2:A10,”=”&TODAY()+7)
Đếm ngày trong một khoảng thời gian =COUNTIF(B2:B10, “>=6/7/2014”)-COUNTIF(B2:B10, “>6/1/2014”)

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:

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 <> “”))

SỬ DỤNG COUNTIF VỚI CHUỖI, SỐ VÀ THAM CHIẾU TRONG EXCEL

Thay vì gõ trực tiếp điều kiện vào tham số thứ 2 của hàm COUNTIF, chúng ta có thể thay điều kiện này bằng 1 con số hoặc bằng tham chiếu đến 1 cell trong Excel. Ví dụ như sau:

sử dụng countif với số

Sử dụng countif với địa chỉ tham chiếu

SỬ DỤNG COUNTIF VỚI DẤU SAO TRONG ĐIỀU KIỆN

Đôi khi, chúng ta sẽ muốn đếm xem trong cột B, có bao nhiêu ô chứa 1 chuỗi nào đó. Ví dụ như sau:

Đếm xem trong cột B có bao nhiêu ô chứa chữ “Brown”. Điều này, chúng ta cũng có thể sử dụng hàm COUNTIF với cú pháp:

=COUNTIF(D2:D10, “*Brown*”)

sử dụng countif với dấu sao

Ý nghĩa của kí tự dấu * trong trường hợp này như sau: Trong phần điều kiện của hàm COUNTIF, chúng ta viết dấu * ở cả 2 đầu của chữ “Brown”, dấu * sẽ đại diện cho 0 hay nhiều kí tự khác. Nếu chúng ta coi “*Brown*” như một điều kiện thì những trường hợp sau đều thoả mãn điều kiện và đều được đếm bởi COUNTIF:

  • ABC Brown
  • Brown ABC
  • ABC Brown XYZ

Qua đây, chúng ta suy ra được, để viết điều kiện:

  • Đếm số ô bắt đầu bằng chữ Brown, điều kiện này sẽ được viết như sau: “Brown*”
  • Đếm số ô kết thúc bằng chữ Brown, điều kiện này sẽ được viết như sau: “*Brown”

Ngoài dấu * là ký tự đại diện cho bất kì số lượng kí tự nào, chúng ta có dấu ? sẽ đại diện cho 1 kí tự duy nhất. Ví dụ như sau: đếm số ô có chính xác 3 kí tự liền trước “ABC”

Vậy để đếm số ô có dấu ? hoặc * ở trong thì ta sẽ làm thế nào? Công thức sử dụng cho trường hợp này là:

=COUNTIF(A4:A7,”*~?*”)

=COUNTIF(A4:A7,”*~**”)

ĐẾM SỐ CELL RỖNG HOẶC KHÔNG RỖNG VỚI COUNITF

Để đếm số cell không rỗng ta sử dụng công thức

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

Để đếm số cell rỗng, ta sử dụng công thức sau:

=COUNTIF(range,””)

hoặc

=COUNTBLANK(range)

Ví dụ minh hoạ

SỬ DỤNG COUNTIF VỚI CÁC PHÉP TOÁN SO SÁNH

Để sử dụng COUNTIF với các phép toán so sánh: bằng, lớn hơn, nhỏ hơn, lớn hơn hoặc bằng, nhỏ hơn hoặc bằng. Chúng ta sẽ viết các phép toán này vào tham số điều kiện thứ 2. Ví dụ như sau:

Cột D và cột E đều mang lại kết quả giống nhau, nhưng chúng ta nên sử dụng công thức viết ở cột E, bởi vì điều kiện không bị gán chặt vào trong công thức, khi sửa sẽ rất dễ dàng.

SỬ DỤNG HÀM COUNITF TRONG EXCEL VỚI NGÀY THÁNG

Trường hợp các bạn muốn đếm số đơn hàng sau 1 ngày cụ thể hoặc trước 1 ngày cụ thể hoặc từ 1 ngày cụ thể +/- x ngày, thì bảng sau với các công thức ví dụ và giải thích sẽ giúp các bạn làm điều đó

Ngoài cách viết trực tiếp giá trị ngày tháng cần so sánh vào tham số điều kiện của hàm COUNTIF, ta còn có thể sử dụng hàm TODAY() để đếm:

=COUNTIF(A2:A10, TODAY())

Nếu muốn đếm số ngày trong 1 vùng A1:A100 trong khoảng từ 18 tháng 1 năm 2016 đến 23 tháng 4 năm 2016, ta sử dụng công thức như sau:

=COUNTIF(A1:A100,”>1/18/2016″) – COUNTIF(A1:A100,”>=4/23/2016″)

minh hoạ cho công thức trên:

SỬ DỤNG HÀM COUNTIF TRONG EXCEL VỚI NHIỀU ĐIỀU KIỆN

Ví dụ 1: đếm số sản phẩm có sản lượng từ 5 tấn đến 10 tấn trong ví dụ sau:

Ví dụ 2: Đếm số loại nước hoa quả và salat trong menu sau đây:

SỬ DỤNG COUNTIF ĐỂ PHÁT HIỆN DỮ LIỆU TRÙNG LẶP

VD: tìm trong menu sau đây những món bị lặp, nếu lặp thì ghi chữ “lặp” sang ô bên cạnh

KIỂM TRA DỮ LIỆU CÓ TRONG CẢ 2 CỘT

Ví dụ: kiểm tra các món ăn có trong cả 2 cột menu 1 và 2

=SUMPRODUCT((COUNTIF(A2:A12,B2:B12)>0)*(B2:B12<>””))

Với ví dụ trên, để đếm số món có ở cột B mà KHÔNG có ở trong cột A, ta đổi công thức như sau:

=SUMPRODUCT((COUNTIF(A2:A12,B2:B12)=0)*(B2:B12<>””))

Vậy là qua bài này, các bạn đã tìm hiểu được rất nhiều ví dụ về cách ứng dụng của hàm COUNTIF trong Excel, hi vọng bài viết có ích cho các bạn. Nếu có bất kỳ thắc mắc gì hãy comment ngay dưới bài viết này để chúng tôi có thể giải quyết mọi thắc mắc của các bạn trong thời gian sớm nhất.