Hàm đếm COUNTIF là hàm có điều kiện được sử dụng phổ biến trong Excel. Vậy công thức và cách dùng hàm COUNTIF như thế nào? Hãy cùng Muaban.net tìm hiểu thông qua bài viết dưới đây nhé.
I. Ý nghĩa của hàm COUNTIF trong Excel
COUNTIF là hàm đếm có điều kiện mà bạn có thể sử dụng để đếm các ô trùng lặp hay đếm dữ liệu trong bảng. Thông thường hàm này sẽ được dùng với những bảng thống kê để đếm các ô chứa giá trị thoả mãn được điều kiện cho trước.
II. Công thức hàm COUNTIF trong Excel
Công thức hàm COUNTIF như sau: =COUNTIF(range, criteria)
Trong đó:
- Range: Là vùng đếm dữ liệu buộc phải có.
- Criteria: Là điều kiện đếm của hàm, dữ liệu cần đếm phải được để trong dấu ngoặc kép và dữ liệu đếm có thể là văn bản, kiểu số, tham chiếu ô hoặc một biểu thức.
Xem thêm: Hướng dẫn 7 cách đánh số thứ tự trong Excel chính xác và đơn giản
III. Cách dùng hàm COUNTIF và ví dụ cụ thể
Một số cách dùng hàm COUNTIF bạn có theo tham khảo qua video dưới đây nhé:
1. Hàm COUNTIF cho văn bản (chính xác tuyệt đối)
Công thức đếm theo giá trị văn bản, ta có:
Công thức: =COUNTIF(A3:A6,”Đào”)
Mô tả: Công thức đếm số sản phẩm “Đào” trong vùng dữ liệu A3:A6 kết quả là 1.
2. Hàm COUNTIF với ký tự đại diện (khớp một phần)
Bạn muốn tính số lượng của một từ khóa nào đó mà trong bảng dữ liệu có nhiều biến thể của từ cần tính. Bạn có thể sử dụng ký tự đại diện để đếm, cụm từ hoặc chữ cái nhất định nào đó là một phần của nội dung ô.
Ví dụ: Bạn muốn đếm số lần xuất hiện của quả “Xoài” trong bảng dữ liệu bạn chỉ cần nhập công thức:
= COUNTIF(A2:A8,”*xoài*”)
= 2
Mô tả: Số lần xuất hiện của quả “Xoài” trong vùng dữ liệu A2:A8 được kết quả là 2 bao gồm “Xoài tượng” và “Xoài keo”.
Ta sử dụng dấu (*) để thay thế các giá trị đầu và cuối như ví dụ minh hoạ trên.
Xem thêm: Bật mí cách tạo CV trên word đơn giản, nhanh gọn nhất
3. Đếm các ô bắt đầu hoặc kết thúc bằng các ký tự nhất định
Để biết số ô bắt đầu và kết thúc bằng văn bản nào đó mà không cần phải đếm số ký tự trong ô. Ví dụ tính các ô kết thúc bằng chữ “ed” bạn có thể sử dụng công thức sau:
= COUNTIF (B2: B8, “*ed*”)
= 2.
Mô tả: Số ô kết thúc bằng chữ “ed” trong vùng dữ liệu B2:B8 được kết quả là 2 bao gồm Status của Product 1 và Product 6.
Để đếm số ô bắt đầu hoặc kết thúc bằng ký tự nhất định và chứa một số lượng ký tự nhất định, ta sử dụng dấu hỏi (?) tại điều kiện hàm COUNTIF như sau:
= COUNTIF (A2: A9, “Miss ??????”)
= 5.
Mô tả: Đếm số ô bắt đầu bằng chữ “Miss” và có chính xác 6 ký tự sau đó trong các ô từ A2 đến A9, ta có kết quả là 5.
Xem thêm: Cách cố định cột trong Excel cực đơn giản và chi tiết
4. Ứng dụng hàm COUNTIF cơ bản với chữ cái và số trùng khớp
Ta sẽ sử dụng hàm COUNTIF để đếm những ô chứa giá trị trùng khớp thỏa mãn điều kiện cho trước.
Ví dụ: Đếm số lượng ô chứa giá trị là nữ trong bảng dữ liệu.
Ta có công thức tính như sau:
=COUNTIF($A$1:$B$10,B3)
=3.
Mô tả: Đếm số lượng ô chứa giá trị “Nữ” trong vùng dữ liệu từ A1:B10 được kết quả là 3.
5. Sử dụng COUNTIF kết hợp những kí tự đại diện (wildcard)
Wildcard là ký tự đại diện được sử dụng để thay thế các ký tự khác trong ô, gồm 3 ký tự như dấu sao (*), dấu chấm hỏi (?) và dấu ngã (~).
Ví dụ: Trong bảng dữ liệu muốn đếm số người mang họ “Trương” và số người có tên là “Xuân” thì ta sẽ thực hiện công thức như sau:
Số lượng người họ “Trương”:
=COUNTIF($A$2:$A$9,$E$6&”*”)
= 2.
Mô tả: Công thức đếm số lượng người họ “Trương” trong vùng dữ liệu A2:A9 kết quả là 2.
Số lượng người có tên là “Xuân”:
=COUNTIF($A$2:$A$10,”*”&$E$8)
=1.
Mô tả: Công thức đếm số lượng người tên “Xuân” trong vùng dữ liệu A2:A9 kết quả là 1.
6. Sử dụng hàm COUNTIF đếm với các phép so sánh
Bạn có thể sử dụng điều kiện trực tiếp hoặc điều kiện tham chiếu trong hàm COUNTIF đếm các phép so sánh lớn hơn (>), nhỏ hơn (<), bằng (=), khác (<>), lớn hơn hoặc bằng (≥), bé hơn hoặc bằng (≤).
| Điều kiện | Công thức ví dụ | Mô tả |
| Lớn hơn | =COUNTIF(A2:A10,”>6″) | Đếm ô có giá trị lớn hơn 6 trong dải ô dữ liệu A2:A10 |
| Bé hơn | =COUNTIF(A2:A10,”<6″) | Đếm ô có giá trị bé hơn 6 trong dải ô dữ liệu A2:A10 |
| Bằng | =COUNTIF(A2:A10,”=6″) | Đếm ô có giá trị bằng 6 trong dải ô dữ liệu A2:A10 |
| Khác | =COUNTIF(A2:A10,”<>6″) | Đếm ô có giá trị khác 6 trong dải ô dữ liệu A2:A10 |
| Lớn hơn hoặc bằng | =COUNTIF(A2:A10,”>=6″) | Đếm ô có giá trị lớn hơn hoặc bằng 6 trong dải ô dữ liệu A2:A10 |
| Bé hơn hoặc bằng | =COUNTIF(A2:A10,”<=6″) |
Đếm ô có giá trị bé hơn hoặc bằng 6 trong dải ô dữ liệu A2:A10 |
Ví dụ cụ thể: Muốn đếm ô trong dải dữ liệu từ H2:H7 có giá trị lớn hơn giá trị trong H5 ta sử dụng công thức sau:
=COUNTIF(H2:H7,”>”&H5)
= 2.
Mô tả: Đếm ô trong dải dữ liệu từ H2:H7 có giá trị lớn hơn giá trị trong H5 (Táo) ta được kết quả là 2 bao gồm H6 (Lê với số lượng là 16) và H7 (Mận với số lượng là 17).
Xem thêm: 3 cách tạo bảng trong Excel dễ làm và cực hiệu quả
Tham khảo các tin đăng mua bán nhà đất, cho thuê phòng trọ tại Muaban.net:
7. Đếm số lần xuất hiện của một giá trị trong một danh sách
Công thức đếm giá trị trong một danh sách ta có thể sử dụng công thức sau:
=COUNTIF(B2:B5,11)
=2.
Mô tả: Công thức đếm số giá trị “11” trong vùng dữ liệu B2:B5 kết quả là 2.
IV. Kết hợp hàm COUNTIF với những hàm khác trong Excel
1. Sử dụng hàm RANK kết hợp với hàm COUNTIF
Việc sử dụng hàm RANH.EQ kết hợp với hàm COUNTIF để dừng việc bỏ qua các con số nhưng hàm này cũng sẽ bỏ qua sự trùng lặp thứ hạng. Mặc dù không có bất kỳ số nào lặp lại hai lần nhưng trường hợp hai học sinh có cùng số (%) có thể sẽ xếp hạng khác nhau.
Ví dụ: Xem cách RANK.EQ kết hợp cùng với COUNTIF sẽ có công thức có dạng như sau:
=RANK.EQ(H2,$H$2:$H$8,0)+COUNTIF($H$2:H2,H2) – 1
Kết quả như hình dưới đây.
Không có bất cứ sự trùng lặp nào trong các cấp bậc ở trên. Nhưng, Bích Phượng và Hoàng Tuấn, hai người có cùng % nhưng lại được xếp ở vị trí khác nhau.
Do đó, việc sử dụng RANK.EQ với COUNTIF đã giải quyết được một nửa vấn đề, nhưng chưa thực sự tạo ra kết quả như mình mong muốn.
Xem thêm: Hướng dẫn cách tạo khung bìa Word đơn giản và đẹp nhất
2. Sử dụng hàm SUMPRODUCT với hàm COUNTIF
Để có thể sắp xếp thứ hạng của các bạn sinh viên trong bảng dữ liệu bằng cách gán những thứ hạng giống nhau cho các tỷ lệ phần trăm tương đương nhau mà không bị bỏ qua bất kỳ số nào. Bạn có thể sử dụng hàm SUMPRODUCT với COUNTIF theo công thức ví dụ dưới đây:
=SUMPRODUCT((H2 <= $H$2:$H$9)/COUNTIF($H$2:$H$9,$H$2:$H$9))
Kết quả như hình dưới đây:
Thoạt nhìn công thức có phần phức tạo nhưng đó là cách tối ưu nhất để xếp hạng cho các bạn một cách chính xác. Sử dụng cách này bạn có thể đạt được kết quả như mong muốn với sự trùng lặp thứ hạng và không có bất kỳ số nào bị bỏ qua.
V. Những lưu ý khi dùng hàm COUNTIF trong Excel
Đối với hàm COUNTIF sẽ không phân biệt chữ hoa hay chữ thường trong chuỗi văn bản. Ví dụ, chuỗi “CHUỐI” và chuỗi “chuối” sẽ khớp với cùng các ô giống nhau.
Dùng ký tự đại diện như:
- Dấu hỏi chấm (?): Một dấu chấm hỏi sẽ khớp với một ký tự đơn lẻ bất kỳ nào đó.
- Dấu hoa thị (*): Một dấu hoa thị sẽ khớp với chuỗi bất kỳ.
- Dấu ngã sõng (~): Để tìm dấu chấm hỏi (?) hoặc dấu hoa thị (*) thực sự thì bạn chỉ cần nhập một dấu sóng (~) trước ký tự đó.
Khi bạn thực hiện đếm giá trị văn bản, hãy đảm bảo rằng dữ liệu không chứa khoảng trắng ở đầu và ở cuối.
Xem thêm: Cách cắt ảnh trong Word đơn giản – nhanh chóng – tiện lợi
VI. Một số lỗi thường gặp khi sử dụng hàm COUNTIF
| Lỗi thường gặp | Diễn giải lỗi |
| Trả về giá trị sai đối với chuỗi dài |
Hàm COUNTIF sẽ trả về kết quả sai khi bạn dùng hàm để khớp với các chuỗi dài hơn 255 ký tự. Để có thể khớp được với các chuỗi dài hơn 255 ký tự, hãy dùng hàm CONCATENATE hoặc sử dụng toán tử ghép nối “&”. Ví dụ như: = COUNTIF(B2:B5,”chuỗi dài”&”chuỗi dài khác”). |
| Không trả về kết quả theo mong đợi | Đảm bảo có ghi đối số “Criteria” trong dấu ngoặc. |
| Gặp lỗi #VALUE! khi tham chiếu tới một trang tính khác |
Lỗi này thường sẽ xảy ra khi bạn sử dụng công thức có chứa hàm tham chiếu đến ô hoặc phạm vi trong một sổ làm việc đóng và các ô được tính toán. Để có thể hoạt động thì sổ làm việc khác phải được được mở. |
Bài viết trên Muaban đã tổng hợp cho bạn các cách dùng hàm COUNTIF, những lưu ý khi sử dụng hàm cũng như những lỗi thường gặp khi dùng. Hy vọng qua bài viết này, bạn đã có thêm cho mình kiến thức về công cụ Excel phục vụ cho học tập và làm việc. Hãy theo dõi Muaban.net nhiều hơn để cập nhật những thông tin mới nhất, cũng như các kinh nghiệm về công việc và đời sống.
Xem thêm:
- Tải mẫu bảng báo giá bằng word chuẩn và mới nhất 2023
- 6 cách đánh số trang trong Excel 2003, 2007, 2010, 2016 đơn giản, nhanh chóng nhất
