Hàm COUNTIF trong excel là công cụ làm việc không thể thiếu của kế toán và kiểm toán, giúp kế toán và kiểm toán viên hoàn thành công việc nhanh chóng và chính xác nhất. Nếu chưa thành thạo công cụ tính toán này, hãy tham khảo ngay hướng dẫn cách dùng hàm COUNTIFS trong excel như hàm đếm COUNTIF, hàm COUNTIF nhiều điều kiện, từ cơ bản đến nâng cao và những lỗi sai thường gặp khi sử dụng hàm COUNTIF.
Hàm COUNTIF là gì? Công thức hàm COUNTIF trong Excel
Hàm COUNTIF là loại hàm đếm có điều kiện trong Excel. Bạn có thể sử dụng hàm COUNTIFS để đếm ô trùng lặp, đếm dữ liệu. Thường thì những loại hàm đếm có điều kiện này sẽ dùng với những bảng dữ liệu thống kê, với yêu cầu đếm số ô chứa giá trị thỏa mãn điều kiện cho sẵn.
Cú pháp hàm COUNTIFS
Cú pháp hàm COUNTIFS = COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2],…)
Trong đó:
- criteria_range1 (Bắt buộc): Là phần vùng dữ liệu cần đếm. Có thể chứa số, mảng hoặc vùng tham chiếu có chứa số.
- criteria1 (Bắt buộc): Là điều kiện của vùng dữ liệu criteria_range1.
- criteria_range2 (Tùy chọn): Là phần vùng dữ liệu thứ 2.
- criteria2 (Tùy chọn): Là điều kiện của vùng dữ liệu thứ 2.
Lưu ý:
- Các vùng dữ liệu thứ 2 trở đi phải cùng cột hoặc cùng hàng với vùng dữ liệu thứ nhất. Không bắt buộc phải liền kề nhau.
- Tối đa có thể đặt 127 phần vùng dữ liệu trong hàm.
- Nếu phần vùng dữ liệu là ô trống thì hàm Excel mặc định là giá trị 0.
Ứng dụng hàm COUNTIFS trong Excel
- Đếm số ô dữ liệu theo nhiều điều kiện.
- Lọc giá trị trùng lặp
- Kết hợp dễ dàng với các hàm khác trong Excel để hỗ trợ công việc.
Cách sử dụng hàm COUNTIFS trong Excel bằng các ví dụ cụ thể
Ví dụ 1: Cách sử dụng hàm COUNTIF để đếm số áo xuất đi hơn 40 cái.
Bước 1: Nhập công thức =COUNTIFS(B3:B8,”Áo”,D3:D8,”>40″) tại phần ô tham chiếu.
Bước 2: Nhấn Enter và kiểm tra lại kết quả.
Ví dụ 2: Cách sử dụng hàm COUNTIF tìm kiếm số lượng tên hàng Dưa hấu có sẵn
Công thức như sau: =COUNTIF(B2:B8,”dưa hấu”), sau đó nhấn Enter để thực hiện hàm.
Và kết quả sẽ đếm ra được 2 giá trị có tên là Dưa hấu.
Ví dụ 3: Cách sử dụng hàm COUNTIF tìm số lượng hàng không phải là Dưa hấu có sẵn
Công thức như sau: =COUNTIF(B2:B8,”<>Dưa hấu”) rồi sau đó nhấn Enter để thực hiện hàm. Trong đó “<>” là ký tự khác
Kết quả đếm được là có 5 loại hàng không phải dưa hấu.
Ví dụ 4: Cách sử dụng hàm COUNTIF tìm kiếm đơn hàng Sầu riêng sử dụng ký tự thay thế
Để tìm kiếm mặt hàng bằng cách sử dụng ký tự thay thế thì ở đối số criteria, bạn điền chữ cái đầu của tên mặt hàng và sau đó là ký tự hoa *.
*Lưu ý: Cách này chỉ có thể áp dụng đối với các mặt hàng có chữ cái đầu khác nhau.
Công thức như sau: =COUNTIF(B2:B8,”S*”) rồi sau đó ấn Enter để thực hiện hàm.
Ví dụ 5: Cách sử dụng hàm COUNTIF để tìm kiếm mặt hàng có tên khác ô B3
Ở đây chúng ta cần tìm những mặt hàng có tên khác ô B3 là Xoài bằng cách dùng ký tự “<>” và “&” trước ô tham chiếu, với cú pháp hàm =COUNTIF(B2:B8,”<>”&B3).
Những dạng cơ bản của cách dùng hàm COUNTIF trong Excel
Dạng 1: Hàm COUNTIFS trong Excel với chữ cái và số trùng khớp
Tại đây, chúng ta sẽ sử dụng hàm COUNTIF để đếm các ô chứa giá trị thỏa mãn trùng khớp với điều kiện yêu cầu. Cú pháp tương ứng trong ví dụ:
Đếm số lượng ô từ vùng C2 – C31 có giá trị là “Nam”:
=COUNTIF($C$2:$C$31,H8) hoặc =COUNTIF($C$2:$C$31,”Nam”)
Đếm số lượng ô từ vùng D2 – D31 có giá trị là 20:
=COUNTIF($D$2:$D$31,H12) hoặc =COUNTIF($D$2:$D$31,20)
Lưu ý: Criteria sẽ không phân biệt chữ hoa và chữ thường. Bởi vậy, Nam, NAM, hay nam thì cũng như nhau thôi. Trong nhiều trường hợp, nó rất tiện lợi vì bạn không phải xử lí chính xác dữ liệu. Tuy nhiên, trong nhiều trường hợp, nó lại ảnh hưởng rất lớn tới việc tìm kiếm. Có rất nhiều cách xử lý, đó là thay vì đếm “Nam”, “NAM”, “nam”, hãy thay bằng đếm TRUE và FALSE kết hợp cùng hàm EXACT.
Dạng 2: Sử dụng hàm COUNTIF trong Excel với các phép so sánh lớn, nhỏ, bằng
Đối với việc đếm có sử dụng điều kiện là các phép so sánh lớn hơn (>), nhỏ hơn (<) hoặc bằng (=), có 2 kiểu đặt điều kiện thường được sử dụng luân phiên nhau, đó là điều kiện trực tiếp và điều kiện tham chiếu.
- Với điều kiện trực tiếp, bạn đặt toàn bộ trong dấu nháy kép như sau: “>=a” với >= là phép so sánh, a là giá trị được so sánh.
- Với điều kiện tham chiếu, bạn đặt phép so sánh trong dấu nháy kép, kết hợp cùng ký tự &: “>=”& I8 với >= là phép so sánh, I8 là ô được tham chiếu tới.
Dạng 3: Sử dụng hàm COUNTIF trong Excel kết hợp các ký tự đại diện (Wildcard)
Ký tự đại diện (Wildcard) được sử dụng để thay thế những ký tự khác trong ô. Có 3 ký tự đại diện là dấu sao (*), dấu hỏi (?) và dấu ngã (~).
Cùng phân tích ví dụ:
Trong ví dụ này, ta sẽ dùng hàm COUNTIF để đếm cho 2 trường hợp: Những người có tên là Anh và những người có họ Lê.
Đầu tiên ta sẽ sử dụng dấu (*) để thay thế cho cụm ký tự. Đối với những người họ Lê, ta sử dụng cú pháp “Lê*” (Cú pháp này được hiểu là sau Lê là một cụm ký tự bất kỳ được đại diện bởi *) và cú pháp “*Anh” (Được hiểu rằng trước Anh là một cụm giá trị được đại diện bởi *).
Kết hợp hàm COUNTIF trong Excel cơ bản, bạn sẽ đếm được số lượng ô thỏa mãn điều kiện.
Dạng 4: Ứng dụng hàm COUNTIF trong Excel lọc giá trị trùng lặp
Trong trường hợp trên, kết quả trả về 2 là tên bị trùng lặp và kết quả trả về 1 là tên không bị trùng.
Ví dụ trên là một ứng dụng đơn giản của hàm COUNTIF sử dụng trong lọc email khách hàng, lọc tên… bị trùng lặp.
Xem thêm:
Các lỗi thường gặp khi sử dụng hàm COUNTIF
Khi sử dụng hàm COUNTIF bạn thường gặp phải thông báo lỗi #VALUE! mà không biết cách xử lý như nào. Vậy sau đây, cùng tham khảo tổng hợp những lỗi cơ bản khi sử dụng hàm COUNTIF và những giải pháp khắc phục để sửa lỗi.
1. Lỗi công thức tham chiếu ô trong bảng tính đã đóng
Hàm COUNTIF/COUNTIFS tham chiếu đến ô thuộc vùng dữ liệu của bảng tính bị đóng và kết quả trả về sẽ là thông báo lỗi #VALUE!.
Lưu ý: Lỗi này cũng hay xảy ra tương tự với một vài hàm Excel khác như hàm SUMIF, SUMIFS, COUNTBLANK…
Giải pháp sửa lỗi: Để khắc phục lỗi này, bạn hãy mở bảng tính chứa vùng dữ liệu được được tham chiếu trong công thức, sau đó nhấn phím F9 để làm mới công thức.
Ngoài ra, bạn cũng có thể áp dụng cách này để sửa lỗi #VALUE! khi sử dụng kết hợp với hàm IF và SUM trong công thức mảng
2. Lỗi chuỗi dài hơn 255 ký tự
Giải pháp sửa lỗi: Rút ngắn chuỗi nếu có thể. Nếu không thể rút gọn hơn, hãy sử dụng hàm CONCATENATE hoặc toán tử (&) để chia giá trị thành nhiều chuỗi.
Ví dụ: =COUNTIF(B2:B12,”chuỗi dài”&”chuỗi dài khác”)
3. Lỗi hàm Countif không tính giá trị vùng dữ liệu không liên tục
Giải pháp sửa lỗi: Hàm COUNTIF không tính giá trị trên các vùng dữ liệu không liên tục, và cú pháp của hàm COUNTIF cho phép xác định ô riêng làm tham số đầu tiên. Thay vào đó, bạn có thể sử dụng nhiều hàm kết hợp COUNTIF:
- Công thức sai: =COUNTIF(A1,B3,C4,”>0″)
- Công thức đúng: =COUNTIF(A1,”>0″)+COUNTIF(B3,”>0″) + COUNTIF(C4,”>0″)
Bên cạnh đó, bạn hãy thử dùng hàm INDIRECT để tạo các dữ liệu mảng.
Ví dụ như 2 công thức dưới đây đều cho một kết quả giống nhau:
=SUM(COUNTIF(INDIRECT({“B2:B8″,”D2:C8″}),”=0”))
=COUNTIF($B2:$B8,0) + COUNTIF($C2:$C8,0)
4. Lỗi sử dụng dấu và dấu ngoặc kép trong hàm Countif
Giải pháp sửa lỗi: Cú pháp hàm COUNTIF vốn cực kỳ khó hiểu. Dấu (&) và dấu ngoặc kép (“”) được sử dụng trong công thức để tạo ra chuỗi văn bản cho đối số. Bạn có thể tuân thủ hàm theo các quy tắc dưới đây:
– Nếu bạn sử dụng một số hoặc ô tham chiếu trong điều kiện so sánh chính xác thì không cần dấu (%) và dấu ngoặc kép.
Ví dụ: =COUNTIF (A1:A10,9) hoặc =COUNTIF (A1:A10,C1).
– Nếu phần điều kiện của bạn gồm cả văn bản, ký tự hoặc toán tử kèm theo số, bạn nhớ phải đặt trong dấu ngoặc kép.
Ví dụ như công thức: = COUNTIF (A2: A10, “chanh”) hoặc = COUNTIF (A2: A10, “*”) hoặc = COUNTIF (A2: A10, “> 5”)
– Nếu bạn sử dụng biểu thức có ô tham chiếu hoặc hàm Excel khác, phải sử dụng dấu ngoặc kép (“”) để bắt đầu chuỗi và dấu và (&) để nối và kết thúc chuỗi.
Ví dụ như: =COUNTIF (A2: A10, “>” & D2) hoặc =COUNTIF (A2: A10, “
– Nếu bạn băn khoăn không biết có phải thêm dấu ngoặc kép hoặc dấu &, hãy cứ thử thêm các dấu này. Trong hầu hết mọi trường hợp, dấu & đều sẽ hoạt động.
5. Lỗi sử dụng hàm Countif để đếm ô định dạng màu
Giải pháp sửa lỗi: Cú pháp hàm COUNTIF trong Excel không cho phép sử dụng các điều kiện định dạng làm điều kiện. Cách duy nhất để đếm và tính tổng các ô định dạng màu là bạn sử dụng macro, hay chính xác hơn là sử dụng hàm Excel User-Defined. Ngoài ra, bạn cũng có thể tìm thấy mã hoạt động của các ô định dạng màu theo cách thủ công.
6. Lỗi hàm Excel Countif không hoạt động
Giải pháp sửa lỗi: Giả sử như bạn đã viết công thức đúng nhưng hàm COUNTIF vẫn không hoạt động, hoặc trả về kết quả sai, thử kiểm tra lại các điều kiện như phạm vi, tham chiếu ô, điều kiện, sử dụng dấu & và dấu ngoặc kép (“”).
Cẩn thận khi sử dụng “dấu cách” trong công thức hàm Excel COUNTIF. Khi tạo công thức tính, đặc biệt nên chú ý dấu cách, nếu công thức có dấu cách có thể gây ra lỗi hàm.
Hàm COUNTIFS mang tính ứng dụng cao, là công cụ hỗ trợ đắc lực trong quá trình làm việc, tính toán. Với những hướng dẫn cùng ví dụ cụ thể về cách dùng hàm COUNTIFS trong Excel trên đây, hi vọng bạn có thể ứng dụng vào thực tế công việc hàng ngày thật hiệu quả.
