Cách dùng hàm COUNTIF trong Excel, Google Sheet kèm ví dụ

Hàm COUNTIF trong Excel và Google Sheet là giải pháp giúp bạn thống kê số lượng ô thỏa mãn một điều kiện cụ thể một cách nhanh chóng. Không chỉ giúp hạn chế sai sót so với thao tác đếm thủ công, công cụ này còn tiết kiệm cho bạn rất nhiều thời gian làm việc. Hãy cùng tìm hiểu chi tiết về cú pháp và tham khảo các ví dụ minh họa cực dễ hiểu ngay trong bài viết dưới đây nhé!

Hàm COUNTIF trong Excel là gì?

Hàm COUNTIF trong Excel hay Google Sheet được định nghĩa là một hàm đếm dữ liệu đi kèm với các điều kiện có sẵn. Thông thường hàm COUNTIF sẽ được áp dụng với những bảng dữ liệu thống kê có phần phức tạp. Và người dùng không muốn thực hiện thủ công để hoàn thành chúng. 

Nói cụ thể hơn, bạn có thể áp dụng hàm COUNTIF trong trường hợp muốn đếm số lần lặp lại của một giá trị cụ thể trong bảng hay một vùng dữ liệu cố định. Thậm chí bạn cũng có thể sử dụng hàm đếm dựa trên các điều kiện số học, điều kiện văn bản hay điều kiện kết hợp…

Công thức hàm COUNTIF trong Excel

Khi các điều kiện nhất định được đáp ứng, hàm COUNTIF trong Excel hay Google Sheet sẽ được dùng để đếm thông qua công thức sau: 

=COUNTIF(range;criteria)

Trong đó: 

• Range: Là vùng dữ liệu mà bạn giới hạn lại khi thực hiện đếm, đây là thành phần bắt buộc phải có trong công thức.
• Criterion: Là phần thể hiện điều kiện cần được đáp ứng để một ô dữ liệu của bạn được đếm. Các giá trị không thỏa điều kiện này sẽ được tự động bỏ qua. Ngược lại nếu thỏa nó sẽ được tính thêm 1 lần trong kết quả.

Lưu ý: Phần điều kiện có thể là một số, một chuỗi văn bản, một tham chiếu ô hay một biểu thức trong dấu ngoặc kép.

Hướng dẫn cách sử dụng hàm COUNTIF cơ bản 

Cách dùng hàm COUNTIF trên Excel hay Google Sheet rất đơn giản. Bạn có thể tham khảo 3 trường hợp phổ biến nhất khi ứng dụng hàm COUNTIF qua các ví dụ dưới đây:

Trường hợp 1: Đếm theo giá trị số

Ta có một ví dụ như sau, đề bài yêu cầu bạn phải áp dụng hàm COUNTIF để đếm số lần xuất hiện giá “65.000” trong bảng Giá. 

Để thỏa yêu cầu, bạn cần nhập công thức vào ô theo cú pháp: =COUNTIF(B2:B9;65000)

Với 

• B2:B9 đóng vai trò là Range - cột dữ liệu bạn giới hạn để đếm. 
• 65000 đóng vai trò là Criterion - điều kiện để hàm COUNTIF đếm thêm 1 giá trị là trong vùng dữ liệu tham chiếu xuất hiện số 65000. 

Kết quả hàm COUNTIF đếm được sẽ trả về tại ô công thức được nhập.

Mẹo tách chữ trong excel nhanh chóng và chuẩn xác nhất. Xem ngay!

Trường hợp 2: Sử dụng hàm COUNTIF để đếm theo giá trị text

Ở ví dụ thứ tiếp theo, thay vì giá trị là số như trường hợp trên. Yêu cầu ở ví dụ này sẽ là áp dụng hàm COUNTIF để đếm số lần xuất hiện của từ “Táo” ở hàng Sản phẩm. 

Để thực hiện, bạn cũng sẽ tiến hành nhập công thức với cú pháp: =COUNTIF(A2:A9;'Táo')

Với 

• A2:A9 đóng vai trò là Range - cột dữ liệu bạn giới hạn để đếm. 
• “Táo” đóng vai trò là Criterion - điều kiện để hàm COUNTIF đếm thêm 1 giá trị là trong vùng dữ liệu tham chiếu xuất hiện ký tự Táo. 

Khi này, bạn đang áp dụng hàm COUNTIF để đếm các ô có giá trị khớp hoàn toàn với ký tự “Táo”. Tuy nhiên trong một vài tình huống, nếu bạn muốn đếm luôn cả những biến thể của ký tự cần tìm (đếm giá trị khớp một phần). 

Bạn có thể nhập công thức: =COUNTIF(A2:A9,“*Cam*”)

Kết quả cuối cùng bảng tính trả về sẽ đếm hết tất cả số lần xuất hiện của ký tự “Cam”. Bất kể đó là những ô kết hợp ký tự “Cam” với một từ, cụm từ hoặc chữ cái khác. 

Trường hợp 3: Đếm theo giá trị ngày, tháng, năm

Ở trường hợp này, giá trị cần đếm sẽ là số lần xuất hiện của ngày, tháng, năm. Khi áp dụng hàm COUNTIF để đếm số lần xuất hiện ngày “12/3/2021” ở hàng Ngày giao. 

Tương tự bạn cũng sẽ tiến hành nhập công thức với cú pháp: =COUNTIF(D2:D9;'12/3/2021')

Với 

• D2:D9 đóng vai trò là Range - cột dữ liệu bạn giới hạn để đếm. 
• '12/3/2021’ đóng vai trò là Criterion - điều kiện để hàm COUNTIF đếm thêm 1 giá trị là trong vùng dữ liệu tham chiếu xuất hiện ngày 12/3/2021.

Ngoài 3 trường hợp trên, bạn cũng có thể áp dụng hàm COUNTIF trong một số trường hợp đặc biệt khác. Chúng tôi sẽ giới thiệu trong phần nội dung tiếp theo. 

Cách dùng COUNTIF để đếm ô bắt đầu hoặc kết thúc bằng ký tự nhất định

Nếu bạn đang muốn biết số ô bắt đầu hoặc kết thúc bằng một văn bản hay kiểu ký tự nhất định nào đó. Đồng thời bạn cũng không cần xét đến có bao nhiêu ký tự khác trong ô. Lúc đó, bạn có thể sử dụng các công thức sau khi áp dụng hàm COUNTIF: 

Trường hợp các ô bắt đầu bằng ký tự nhất định

Xét trong ví dụ cụ thể sau, bạn muốn đếm xem có bao nhiêu ô bắt đầu bằng ký tự “Đ” trong vùng dữ liệu C2:C9. 

Bạn có thể nhập công thức như sau: = COUNTIF (C2: C9, “Đ*”)

Trong ví dụ trên, ta đã sử dụng dấu (*) để đại diện cho các ký tự phía sau “Đ” chưa được xác định trước đó. 

Trường hợp các ô kết thúc bằng ký tự nhất định

Xét trong ví dụ cụ thể sau, khi muốn đếm xem có bao nhiêu ô kết thúc bằng ký tự “y” trong vùng dữ liệu C2:C9. 

Bạn có thể nhập công thức như sau: =COUNTIF (C2:C9, “*y”)

Trong ví dụ trên, ta đã sử dụng dấu (*) để đại diện cho các ký tự phía trước “y” chưa được xác định trước đó. 

Xem thêm hàm Xlookup trong Excel là gì, cách sử dụng hàm Vlookup chi tiết tại đây!

Cách sử dụng hàm COUNTIF để đếm với các phép so sánh

Khi áp dụng hàm COUNTIF, bạn có thể sử dụng điều kiện trực tiếp hoặc điều kiện tham chiếu để đếm các phép so sánh lớn hơn (>), nhỏ hơn (<), bằng (=) hay khác (<>). 

Ví dụ minh họa: Tìm kiếm sản phẩm có tên khác với iPhone. Bạn có thể áp dụng ký tự <> để tìm kiếm giá trị KHÔNG PHẢI. 

• Hãy gõ hàm =COUNTIF(B4:B7,'<>iPhone') vào ô muốn hiển thị kết quả.

• Khi bấm Enter, kết quả sẽ trả về như sau.

Ví dụ minh họa: Tương tự nếu muốn đếm xem có bao nhiêu sản phẩm có số lượng bán lớn hơn Apple. 

Chú ý. Trong trường hợp bạn so sánh với giá trị 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.

• Bạn có thể sử dụng cú pháp sau: =COUNTIF(D4:D7,'>'&D5)

• Kết quả sau nhấn Enter

Ta thấy kết kết quả trả về là 2, con số này có ý nghĩa là có 2 sản phẩm có số lượt bán lớn hơn Apple.

Xem thêm hàm tính trung bình trong excel dành cho dân văn phòng

Cách dùng hàm COUNTIF để lọc giá trị trùng lặp

Một tính năng khác của hàm COUNTIF mà không phải ai cũng biết đó là lọc các thông tin trùng lặp trong bảng dữ liệu. Bạn có thể ứng dụng hàm COUNTIF để lọc email, lọc tên trùng lặp…trong bảng data khách hàng. 

Ta xét ví dụ sau: Yêu cầu của đề bài là tính tần số lặp lại của mỗi dữ liệu trong dải ô A2:A9 thông qua hàm COUNTIF.

Bạn sẽ tiến hành nhập công thức: =COUNTIF($A$2:$A$9,A2) vào ô đầu tiên trong cột Occurrences.

Sau đó bạn thực hiện sao chép cú pháp cho các ô còn lại trong cột. 

Kết quả bạn thu được sẽ thể hiện số lần lặp lại của mỗi giá trị trong ô Items.

Kết hợp hàm COUNTIF trong Excel với các hàm khác

Khi sử dụng hàm COUNTIF, sẽ có những trường hợp bạn cần đánh giá đồng thời với nhiều điều kiện khác nhau. Dưới đây bạn sẽ tìm thấy một số ví dụ về công thức minh họa cách sử dụng hàm COUNTIF kết hợp với các hàm khác trong Excel đánh giá nhiều điều kiện.

Hàm AND 

Hàm AND khi kết hợp với hàm COUNTIF sẽ dùng để để kiểm tra các điều kiện logic có đồng thời đúng hay không. Sẽ có 2 trường hợp xảy: 

• Kết quả sẽ trả về True nếu các điều kiện đó đều đúng hết.
• Kết quả sẽ trả về False nếu 1 trong số đó sai hay tất cả đều sai.

Công thức: =AND(COUNTIF(range;criteria);COUNTIF(range;criteria);...)

Với biểu thức là các thành phần tương tự như hàm COUNTIF cơ bản.

Ví dụ: Ta có một bản thống kê hàng tiêu dùng. Yêu cầu của đề là kiểm tra 2 điều kiện này có đồng thời đúng hay không.

Điều kiện 1: Đếm “số sản phẩm có nhà cung cấp là Bánh kẹo” có lớn hơn 3.

Điều kiện 2: Đếm “số lượng sản phẩm trên 40” có lớn hơn 4.

Ta có công thức kết hợp sau: 

=AND(COUNTIF(B2:B15;'Bánh kẹo')>3;COUNTIF(C2:C15;'>40')>4)

Kết quả sau khi chạy hàm kết hợp: TRUE. Nghĩa là hai điều kiện trên đều đúng.

Hàm OR

Hàm OR khi kết hợp với hàm COUNTIF sẽ dùng để để kiểm tra các điều kiện có ít nhất cái nào đúng hay không. Sẽ có 2 trường hợp xảy: 

• Kết quả sẽ trả về True nếu ít nhất một trong những điều kiện đầu vào là đúng.
• Kết quả sẽ trả về False nếu tất cả điều kiện đầu vào đều sai.

Công thức: =OR(COUNTIF(range;criteria);COUNTIF(range;criteria);...)

Với biểu thức là các thành phần tương tự như hàm COUNTIF cơ bản.

Ví dụ: Cũng là danh sách thống kê hàng tiêu dùng. Yêu cầu của đề là kiểm tra 2 điều kiện này có ít nhất 1 điều kiện đúng hay không. 

Điều kiện 1: Đếm “số sản phẩm có nhà cung cấp là Gia vị” có lớn hơn 3.

Điều kiện 2: Đếm “số lượng sản phẩm là 33” có lớn hơn 2.

Ta có công thức kết hợp sau: 

=OR(COUNTIF(B2:B15;'Gia vị')>3;COUNTIF(C2:C15;33)>2)

Kết quả sau khi chạy hàm kết hợp: TRUE

Nghĩa là có ít nhất 1 trong 2 điều kiện ở trên đúng. 

Hàm LEFT, RIGHT, MID

Hàm Left, Right, Mid là hàm được sử dụng để tìm kiếm và truy xuất dữ liệu trong Excel. Bạn cũng có thể kết hợp hàm COUNTIF với những hàm trên. 

Đây là bài viết về hàm value trong excel. đừng bỏ lỡ!

Ví dụ khi kết hợp hàm COUNTIF với hàm Left:

Đếm số trạng thái “Đã giao” và “Đang giao” trong dải ô D2:D9 bằng cách đếm ký tự “Đ” bên trái của mỗi cụm từ. 

Ta có công thức: =COUNTIF(ARRAYFORMULA(LEFT(D2:D9,1)),'Đ')

Với 

• ARRAYFORMULA là hàm dùng để sao chép công thức của hàm LEFT cho các ô còn lại.
• $D$2:$D$9 là vùng dữ liệu tham chiếu
• 'Đ' là ký tự để tham chiếu

Ta thấy kết quả trả về sẽ là 6.

Ví dụ khi kết hợp hàm COUNTIF với hàm Right:

Đếm số lần xuất hiện của trạng thái “Hủy” trong dải ô D2:D9 bằng cách đếm ký tự “Y” bên phải của mỗi chữ. 

Ta có công thức: =COUNTIF(ARRAYFORMULA(RIGHT(D2:D9,1)),'Y')

Với các thành phần tương tự như ví dụ của hàm left. 

Ta thấy kết quả trả về sẽ là 2.

Ví dụ khi kết hợp hàm COUNTIF với hàm MID:

Đếm số lần xuất hiện sản phẩm “Bưởi” trong dải ô B2:B9 bằng cách đếm ký tự “Ư” ở giữa của mỗi chữ “Bưởi”.

Ta có công thức: =COUNTIF(ARRAYFORMULA(MID(B2:B9,2,1)),'Ư')

Với các thành phần tương tự như ví dụ của hàm left. 

Ta thấy kết quả trả về sẽ là 1.

Hàm INDEX, MATCH

Ví dụ: Đếm số lần xuất hiện sản phẩm 'Chuối' bằng cách tham chiếu dữ liệu bên ngoài dải ô đã chọn.

Công thức:=COUNTIF(A2:A9,INDEX(A12:A15,MATCH(B14,B12:B15,0)))

• Dùng hàm MATCH để dò vị trí ô Mã SP của Chuối.
• Lồng hàm INDEX vào hàm MATCH để lấy giá trị Mã SP của Chuối. 
• Lồng hàm COUNTIF vào hàm INDEX để đếm số lần xuất hiện trong cột sản phẩm của chuối. 

Ta thấy kết quả trả về sẽ là 2. 

Hàm SUM

Hàm SUM được định nghĩa là hàm tính tổng trong Excel. Khi kết hợp hàm SUM với hàm COUNTIF là bạn đang tính tổng của số lần đếm được trong vùng dữ liệu đó.

Công thức: =SUM(COUNTIF(criteria1,range1), COUNTIF(criteria2,range2),...)

Ví dụ: Tính tổng số lần xuất hiện của sản phẩm “Táo” và sản phẩm “Chuối” trong cột Sản phẩm. 

Công thức: =SUM(COUNTIF(B2:B9,'Táo'),COUNTIF(B2:B9,'Chuối'))

Với: 

• COUNTIF(B2:B9,'Táo') là đếm số lần xuất hiện của Táo trong cột sản phẩm.
• COUNTIF(B2:B9,'Chuối') là đếm số lần xuất hiện của Chuối trong cột sản phẩm

Ta thấy kết quả trả về sẽ là 5. 

Hàm VLOOKUP

Đếm số lần xuất hiện của trạng thái “Đang giao” trong cột Trạng thái.

Ta có công thức: =COUNTIF(ARRAYFORMULA(VLOOKUP(D2:D9,D2:D9,1,FALSE)),E11)

Với ARRAYFORMULA là hàm dùng để sao chép công thức của hàm VLOOKUP cho các ô còn lại.

Ta thấy kết quả trả về sẽ là 5.

Cách sử dụng hàm đếm ô trống

Trong quá trình làm việc với Excel hay Google Sheet, đôi khi bạn sẽ cần thống kê số lượng ô chứa dữ liệu hoặc các ô bị bỏ trống trong một bảng tính lớn. Thay vì tốn thời gian dò tìm và đếm thủ công, bạn có thể áp dụng ngay các hàm xử lý ô trống sau:

Hàm đếm ô không trống COUNTA

Hàm COUNTA được sử dụng để đếm số lượng các ô không trống (bao gồm cả các ô chứa giá trị chuỗi văn bản và số) trong một vùng dữ liệu cố định.

Cú pháp hàm COUNTA: =COUNTA(value1, value2,...)

Trong đó: value1, value2,… là những ô cần đếm hoặc một vùng dữ liệu cần đếm. Bạn có thể đếm tối đa lên tới 255 đối số (đối với các phiên bản Excel từ 2007 trở về sau) và tối đa 30 đối số (đối với Excel 2003 trở về trước).

Ví dụ: Giả sử bạn có một bảng danh sách thống kê tình trạng nộp bài của sinh viên. Bây giờ, bạn muốn biết chính xác số lượng sinh viên đã nộp bài (tức là cột trạng thái đã có link bài tập).

Bạn chỉ cần nhập công thức sau vào ô kết quả: =COUNTA(C2:C11)

Kết quả: Hệ thống sẽ trả về giá trị là 4, nghĩa là có tổng cộng 4 sinh viên đã hoàn thành và nộp bài tập.

Hàm đếm ô trống COUNTBLANK

Ngược lại với COUNTA, hàm COUNTBLANK được dùng để đếm các ô trống (những ô rỗng, không chứa bất kỳ dữ liệu nào) trong một vùng được chọn.

Cú pháp hàm COUNTBLANK: =COUNTBLANK(range)

Trong đó: range là vùng hoặc phạm vi dữ liệu mà bạn cần đếm số lượng các ô trống.

Ví dụ: Vẫn sử dụng bảng danh sách thống kê tình trạng nộp bài của sinh viên như trên. Lần này, bạn muốn biết số lượng sinh viên chưa nộp bài (tức là cột trạng thái vẫn chưa có link bài tập, ô đang bị rỗng).

Kết quả: Hệ thống sẽ tự động đếm và trả về hiển thị có tổng cộng 6 sinh viên chưa nộp bài tập.

Các lỗi thường gặp khi dùng hàm COUNTIF và cách khắc phục

Khi sử dụng hàm đếm có điều kiện trong Excel hay Google Sheet, đôi khi bạn sẽ gặp phải một số thông báo lỗi khiến kết quả không hiển thị như mong muốn. Dưới đây là tổng hợp các lỗi phổ biến và cách khắc phục nhanh chóng để đảm bảo bảng tính của bạn luôn vận hành trơn tru:

Lỗi #NAME?

Trường hợp bị lỗi #NAME? là do bạn đã gõ sai tên hàm hoặc vùng dữ liệu.

Ví dụ: Công thức =COUNTIF(B2B9;'Táo') đã gõ sai vùng dữ liệu (thiếu dấu hai chấm (:)).

Lỗi #N/A

Nếu trong trường hợp lỗi #N/A hàm COUNTIF trong Excel là bởi vì không tìm thấy giá trị cần tìm.

Chẳng hạn như hình dưới đây, là do thừa dấu chấm phẩy (;) nên hàm COUNTIF đã bị dư 1 đối số. Bạn chỉ cần bỏ dấu chấm phẩy đi để không bị lỗi #N/A nữa.

Lỗi #ERROR!

Khi xảy ra lỗi #ERROR! là do bạn đã nhập sai cú pháp. Chẳng hạn như công thức =COUNTIF(C2:C9;22*) Wildcard thay thế cho mọi ký tự chuỗi nhưng định dạng kiểu số thì không.

Do đó bạn sẽ gặp phải lỗi #ERROR! nếu không kiểm tra kỹ dữ liệu trước khi nhập công thức. 

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

Để quá trình thống kê dữ liệu diễn ra thuận lợi và tránh các sai sót không đáng có, bạn cần lưu ý một số điểm quan trọng sau:

• Không phân biệt chữ hoa và chữ thường: Khi đếm các chuỗi văn bản, hàm sẽ coi 'học' và 'HỌC' là hoàn toàn giống nhau.
• Hỗ trợ ký tự đại diện: Bạn có thể sử dụng dấu ? để đại diện cho một ký tự bất kỳ. Hoặc dấu * để đại diện cho một chuỗi ký tự bất kỳ. Nếu bạn muốn đếm chính xác ký tự ? hoặc * có trong ô, hãy thêm ~ ngay phía trước chúng.
• Kiểm tra khoảng trắng thừa: Khi đếm giá trị văn bản, hãy đảm bảo dữ liệu của bạn không chứa các khoảng trắng thừa ở đầu hoặc cuối chuỗi để tránh việc đếm bị sót.
• Nên sử dụng phạm vi có tên (Named Range): Việc này giúp công thức của bạn ngắn gọn và dễ quản lý hơn.

Nắm vững những lưu ý nhỏ nhưng có võ này sẽ giúp bạn sử dụng hàm COUNTIF một cách chuyên nghiệp, xử lý dữ liệu linh hoạt và chính xác.

Những câu hỏi thường gặp về hàm COUNTIF

Dưới đây là giải đáp nhanh cho một số thắc mắc phổ biến khi người dùng mới làm quen với hàm đếm có điều kiện này:

Hàm COUNTIF có phân biệt chữ hoa, chữ thường không?

Không, hàm COUNTIF không phân biệt chữ hoa hay chữ thường khi xử lý dữ liệu. Ví dụ, điều kiện đếm là 'EXCEL' hay 'excel' thì hệ thống đều trả về kết quả giống hệt nhau.

Sự khác nhau giữa COUNT, COUNTA, COUNTIF và COUNTIFS là gì?

Hàm COUNT chỉ đếm các ô chứa số, COUNTA đếm mọi ô không bị trống, COUNTIF đếm các ô thỏa mãn một điều kiện. Trong khi COUNTIFS đếm dữ liệu dựa trên nhiều điều kiện cùng lúc.

Cú pháp COUNTIF trong Google Sheet có khác Excel không?

Hoàn toàn không. Cú pháp, cách hoạt động và các đối số của hàm này trong Google Sheet và Microsoft Excel là giống hệt nhau, bạn có thể áp dụng linh hoạt cho cả hai nền tảng.

Kết luận

Nắm vững cách sử dụng hàm COUNTIF trong Excel và Google Sheet sẽ giúp bạn thống kê dữ liệu nhanh chóng, chính xác và tối ưu hóa hiệu suất làm việc. Hy vọng với những hướng dẫn chi tiết cùng các ví dụ minh họa trực quan ở trên, bạn đã có thể tự tin áp dụng thành thạo công thức này vào thực tế. Để cập nhật thêm nhiều thủ thuật tin học văn phòng hay và các mẹo vặt công nghệ hữu ích khác, đừng quên tiếp tục theo dõi Điện Thoại Vui nhé!