Hàm COUNTIFS trong Excel là một công cụ mạnh mẽ để đếm số lượng ô thỏa mãn nhiều điều kiện khác nhau. Bài viết này sẽ hướng dẫn bạn cách sử dụng COUNTIFS và COUNTIF với nhiều điều kiện, kết hợp logic AND và OR, cùng với các ví dụ minh họa chi tiết.
Hàm COUNTIFS: Cú Pháp và Cách Sử Dụng
Cú pháp của hàm COUNTIFS như sau:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)Trong đó:
criteria_range1: Phạm vi ô đầu tiên cần kiểm tra điều kiện. (Bắt buộc)criteria1: Điều kiện áp dụng chocriteria_range1. Có thể là số, tham chiếu ô, chuỗi văn bản, mảng hoặc một hàm Excel khác. (Bắt buộc)[criteria_range2, criteria2], ...: Các phạm vi và điều kiện bổ sung. Bạn có thể thêm tối đa 127 cặp phạm vi/điều kiện. (Tùy chọn)
alt: Minh họa cú pháp hàm COUNTIFS trong Excel, cho thấy các đối số phạm vi điều kiện và điều kiện tương ứng.
Lưu ý khi sử dụng COUNTIFS:
COUNTIFSđếm các ô thỏa mãn tất cả các điều kiện đã chỉ định.- Các phạm vi điều kiện phải có cùng số hàng và cột.
- Có thể đếm các vùng dữ liệu liền kề hoặc không liền kề.
- Nếu một ô trong điều kiện là ô trống,
COUNTIFScoi nó như giá trị 0. - Có thể sử dụng ký tự đại diện
*(đại diện cho chuỗi ký tự bất kỳ) và?(đại diện cho một ký tự đơn) trong điều kiện.
Đếm Ô với Nhiều Tiêu Chí (Logic AND)
Ví dụ 1: Các tiêu chí khác nhau
Giả sử bạn có một bảng danh sách sản phẩm với thông tin về số lượng tồn kho và số lượng đã bán. Bạn muốn đếm số lượng sản phẩm còn tồn kho (cột B > 0) nhưng chưa bán được (cột C = 0).
Công thức sử dụng:
=COUNTIFS(B2:B7, ">0", C2:C7, "=0")alt: Ứng dụng hàm COUNTIFS để đếm số lượng sản phẩm tồn kho lớn hơn 0 và số lượng bán bằng 0, minh họa logic AND trong Excel.
Ví dụ 2: Các tiêu chí giống nhau
Nếu bạn muốn đếm số lượng sản phẩm có giá trị bằng 0 ở cả cột B và cột C, bạn vẫn cần chỉ định từng cặp phạm vi/điều kiện riêng biệt:
=COUNTIFS($B$2:$B$7, "=0", $C$2:$C$7, "=0")alt: Sử dụng hàm COUNTIFS để đếm các mục có giá trị bằng 0 ở cả hai cột, thể hiện cách chỉ định rõ ràng các điều kiện trong Excel.
Đếm Ô với Nhiều Tiêu Chí (Logic OR)
Để đếm các ô thỏa mãn ít nhất một trong các điều kiện (logic OR), bạn có thể sử dụng một trong hai phương pháp sau:
Phương pháp 1: Cộng các hàm COUNTIF/COUNTIFS
Nếu bạn muốn đếm các đơn hàng có trạng thái “Đã hủy” hoặc “Đang chờ xử lý”, bạn có thể sử dụng công thức sau:
=COUNTIF($C$2:$C$11,"Đã hủy")+COUNTIF($C$2:$C$11,"Đang chờ xử lý")alt: Đếm số lượng đơn hàng có trạng thái “Đã hủy” hoặc “Đang chờ xử lý” bằng cách cộng các hàm COUNTIF, minh họa logic OR trong Excel.
Nếu mỗi hàm chứa nhiều hơn một điều kiện, hãy sử dụng COUNTIFS thay vì COUNTIF. Ví dụ, để đếm số đơn hàng “Đã hủy” hoặc “Đang chờ xử lý” cho sản phẩm “Táo”, sử dụng công thức:
=COUNTIFS($A$2:$A$11,"Táo",$C$2:$C$11,"Đã hủy")+COUNTIFS($A$2:$A$11,"Táo",$C$2:$C$11,"Đang chờ xử lý")alt: Sử dụng COUNTIFS kết hợp với logic OR để đếm số lượng đơn hàng “Đã hủy” hoặc “Đang chờ xử lý” cho sản phẩm “Táo”.
Phương pháp 2: Sử dụng SUM và COUNTIFS với hằng mảng
Để viết công thức ngắn gọn hơn khi có nhiều tiêu chí, hãy liệt kê tất cả các tiêu chí trong một mảng và sử dụng hàm SUM kết hợp với COUNTIFS:
=SUM(COUNTIFS(range,{"criteria1","criteria2","criteria3",...}))Ví dụ, để đếm các đơn hàng có trạng thái “Đã hủy”, “Đang chờ xử lý” hoặc “Đang giao hàng”, công thức sẽ là:
=SUM(COUNTIFS($C$2:$C$11,{"Đã hủy","Đang chờ xử lý","Đang giao hàng"}))alt: Minh họa công thức SUM COUNTIFS với hằng mảng để đếm các đơn hàng có trạng thái “Đã hủy”, “Đang chờ xử lý” hoặc “Đang giao hàng”.
Tương tự, bạn có thể đếm dựa trên nhiều cặp phạm vi/điều kiện. Ví dụ, để đếm số lượng đơn hàng “Táo” có trạng thái “Đã hủy”, “Đang chờ xử lý” hoặc “Đang giao hàng”, sử dụng công thức:
=SUM(COUNTIFS($A$2:$A$11,"Táo",$C$2:$C$11,{"Đã hủy","Đang chờ xử lý","Đang giao hàng"}))alt: Sử dụng SUM COUNTIFS với nhiều cặp phạm vi/điều kiện để đếm số lượng đơn hàng “Táo” có trạng thái “Đã hủy”, “Đang chờ xử lý” hoặc “Đang giao hàng”.
Đếm Số Trong Khoảng Cụ Thể
Phương pháp 1: Sử dụng hàm COUNTIFS
Để đếm các số nằm trong khoảng từ 5 đến 10 (không bao gồm 5 và 10) trong các ô C2 đến C10, sử dụng công thức:
=COUNTIFS(C2:C10,">5",C2:C10,"<10")Để bao gồm cả 5 và 10, sử dụng “>=” và “<=”:
=COUNTIFS(B2:B10,">=5",B2:B10,"<=10")alt: Minh họa cách dùng hàm COUNTIFS để đếm các số nằm giữa một khoảng giá trị cụ thể, ví dụ từ 5 đến 10.
Phương pháp 2: Sử dụng hàm COUNTIF
Bạn cũng có thể sử dụng hàm COUNTIF lồng ghép để đạt được kết quả tương tự:
=COUNTIF(C2:C10,">5")-COUNTIF(C2:C10,">=10")Công thức này đếm số lượng số lớn hơn 5 và trừ đi số lượng số lớn hơn hoặc bằng 10.
Sử Dụng Tham Chiếu Ô Trong Công Thức COUNTIFS
Khi sử dụng các toán tử logic (“>”, “=”) với tham chiếu ô trong công thức COUNTIFS, hãy đặt toán tử trong ngoặc kép và thêm dấu & trước tham chiếu ô để tạo thành một chuỗi văn bản.
Ví dụ, để đếm số đơn hàng “Táo” có số tiền lớn hơn $200, với criteria_range1 trong A2:A11 và criteria_range2 trong B2:B11, bạn có thể sử dụng công thức:
=COUNTIFS($A$2:$A$11,"Táo",$B$2:$B$11,">200")Hoặc, bạn có thể nhập giá trị “Táo” vào ô F1 và giá trị 200 vào ô F2, sau đó tham chiếu đến các ô này trong công thức:
=COUNTIFS($A$2:$A$11,$F$1,$B$2:$B$11,">"&$F$2)alt: Ví dụ về cách sử dụng tham chiếu ô trong công thức COUNTIFS để đếm đơn hàng “Táo” có số tiền lớn hơn một giá trị tham chiếu.
Sử Dụng Ký Tự Đại Diện Trong COUNTIFS
Trong công thức Excel, bạn có thể sử dụng các ký tự đại diện sau:
?: Đại diện cho một ký tự đơn bất kỳ.*: Đại diện cho một chuỗi ký tự bất kỳ.
Lưu ý: Nếu bạn muốn đếm các ô chứa dấu hỏi hoặc dấu hoa thị, hãy thêm dấu ngã (~) trước ký tự đó.
Ví dụ, nếu bạn có danh sách các dự án trong cột A và muốn đếm số dự án đã ký kết (cột B chứa tên người) có ngày kết thúc (cột D khác rỗng), bạn có thể sử dụng công thức:
=COUNTIFS(B2:B10,"*",D2:D10,"<>")alt: Ứng dụng COUNTIFS với ký tự đại diện để đếm các mục chứa văn bản bất kỳ trong một cột và các ô không trống trong một cột khác.
COUNTIFS và COUNTIF Với Nhiều Tiêu Chí Ngày Tháng
Ví dụ 1: Đếm ngày trong một khoảng thời gian cụ thể
Để đếm số ngày trong khoảng thời gian từ 01/06/2014 đến 07/07/2014 trong các ô C2 đến C10, bạn có thể sử dụng công thức:
=COUNTIFS(C2:C9,">=1/6/2014",C2:C9,"<=7/7/2014")Hoặc sử dụng COUNTIF:
=COUNTIF(C2:C9,">=1/6/2014")-COUNTIF(C2:C9,">7/7/2014")alt: Sử dụng COUNTIFS để đếm số lượng ngày trong một khoảng thời gian cụ thể, ví dụ từ 1/6/2014 đến 7/7/2014.
Ví dụ 2: Đếm ngày với nhiều điều kiện
Để đếm số lượng sản phẩm được mua sau ngày 20/05/2014 và giao hàng sau ngày 01/06/2014, sử dụng công thức:
=COUNTIFS(C2:C9,">20/5/2014",D2:D9,">1/6/2014")alt: Sử dụng COUNTIFS để đếm số lượng ngày thỏa mãn nhiều điều kiện khác nhau về thời gian mua và giao hàng.
Ví dụ 3: Đếm ngày với nhiều điều kiện dựa trên ngày hiện tại
Bạn có thể sử dụng hàm TODAY() kết hợp với COUNTIF để tính toán dựa trên ngày hiện tại. Ví dụ, công thức sau sẽ cho biết số lượng sản phẩm đã được mua nhưng chưa giao hàng:
=COUNTIFS(C2:C9,"<= "&TODAY(),D2:D9,">"&TODAY())alt: Áp dụng COUNTIFS kết hợp với hàm TODAY() để đếm số lượng sản phẩm đã mua trước ngày hiện tại và chưa được giao.
Hy vọng bài viết này đã cung cấp cho bạn những kiến thức cần thiết để sử dụng COUNTIF và COUNTIFS một cách hiệu quả trong Excel.
