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

Khi xử lý số liệu hàm COUNTIF trong Excel và Google Sheet là một công cụ hữu ích giúp bạn tiết kiệm thời gian. Hàm COUNTIF sẽ thay bạn đếm số lượng các giá trị trong một phạm vi cụ thể thay vì cách làm thủ công trước đây. Hãy cùng chúng tôi tìm hiểu hàm COUNTIF và cách sử dụng chúng ngay trong bài viết này nhé!

Hàm COUNTIF 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 là gì? 

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. 

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.

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. 

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.

Tại sao hàm COUNTIF trong Excel không ra kết quả

Một vài trường hợp bị lỗi không hiển thị kết quả khi bạn gõ hàm COUNTIF. Và dưới đây là những thủ thuật laptop hay chia sẻ về lỗi hàm COUNTIF trong Excel thường thấy!

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. 

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

1. Hàm COUNTIF có đếm nhiều điều kiện được không?

Hàm COUNTIF là hàm đếm 1 điều kiện. Do đó với hàm COUNTIF bạn không thể đếm được nhiều điều kiện khác nhau cũng một lúc.

Trong trường hợp này, bạn có thể sử dụng hàm mở rộng của COUNTIF, chính là hàm COUNTIFs. Hàm COUNTIFS có thể giúp bạn đếm nhiều điều kiện khác nhau trong cùng một phạm vi hay các phạm vi khác nhau

2. Hàm COUNTIF và COUNTIFS, COUNTA có gì khác nhau?

COUNTIF, COUNTIFs, COUNTA đều là công cụ cho phép bạn đếm các ô có chứa ký tự số, chữ hoặc chứa các ký tự có điều kiện cho trước. Tuy nhiên nó lại có sự khác nhau về công dụng cũng như công thức. 

• Hàm Counta: Là hàm đếm các ô có chứa ký tự bao gồm số và chữ.

Công thức: =COUNTA(value1, value2, value3...).

• Hàm COUNTIF: Là hàm đếm các ô chứa dữ liệu có một điều kiện cho trước.

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

• Hàm COUNTIFs: Hàm đếm các ô chứa dữ liệu có nhiều điều kiện cho trước.

Công thức: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

Kết luận

Trong công việc và học tập, nếu bạn cần xử lý số liệu thì hàm COUNIF trong Excel là công cụ không thể thiếu. Chính vì thế, hãy thành thạo những công thức cơ bản trên để hoàn thành công việc một cách tốt nhất nhé. Chúc bạn thành công và đừng quên theo dõi nhiều công thức Excel hay và hữu ích tại website của chúng tôi mỗi ngày.