Giới thiệu hàm COUNTIF và COUNTIFS
Nếu bạn đã nắm chắc hàm SUMIF và SUMIFS thì bạn chỉ cần bỏ cột cần tính tổng ra khỏi công thức thì kiến thức của hàm SUMIF và SUMIFS trở thành kiến thức hàm COUNTIF và COUNTIFS
Hàm COUNTIF: Dùng để đếm các giá trị lặp lại trong một phạm vi dựa trên một điều kiện duy nhất.
= COUNTIF(dải ô, tiêu chí)
- dải ô: Phạm vi để kiểm tra điều kiện.
- tiêu chí: Điều kiện cần thỏa mãn.
Hàm COUNTIFS: Dùng để đếm các giá trị lặp lại trong một phạm vi dựa trên nhiều điều kiện.
= COUNTIFS(dải_ô_tiêu_chí1, tiêu chí1, [dải ô_tiêu chí2, …], [tiêu chí2, …])
- dải_ô_tiêu_chí: Phạm vi để kiểm tra điều kiện.
- tiêu chí: Điều kiện cần thỏa mãn.
COUNTIF và COUNTIFS cơ bản
Tiêu chí/Điều kiện đếm là Văn bản:
Ví dụ bạn muốn đếm số lấn bán hàng của nhân viên Vũ Hoàng Gia thì chữ Vũ Hoàng Gia phải đưa trong nháy kép:
=COUNTIF(C:C;“Vũ Hoàng Gia”)
Nếu tiêu chí đặt ở ô J2 chẳng hạn, thì công thức là:
=COUNTIF(C:C;J2)
Tiêu chí/Điều kiện đếm là Số:
Ví dụ bạn muốn đếm số lần bán hàng của Tổ 1, thì số không phải đưa trong nháy kép:
=COUNTIF(B:B;1)
Nếu tiêu chí đặt ở ô J3 chẳng hạn, thì công thức là:
=COUNTIF(B:B;J3)
Tiêu chí/Điều kiện đếm là Ngày:
Ví dụ bạn muốn tính tổng số tiền của ngày 10/02/2024, thì ngày phải đưa trong ngoặc kép:
=COUNTIF(A:A;”10/02/2024″)
Nếu tiêu chí đặt ở ô J5 chẳng hạn, thì công thức là:
=COUNTIF(A:A;J5)
Lưu ý: Cách viết ngày 10/02/2024 trong công thức COUNTIF phụ thuộc vào ngôn ngữ mà bạn chọn. Chẳng hạn, nếu bạn chọn Việt Nam thì là 10/02/2024, còn bạn chọn Hoa Kỳ thì là 02/10/2024
Nếu bạn quy đổi ngày 10/02/2024 sang số là 45332 (cách quy đổi xem ở đây)
=COUNTIF(A:A;45332)
Nếu bạn đếm từ ngày đến ngày, thì là 2 điều kiện, nên bạn dùng COUNTIF:
=COUNTIFS(A:A;”>=01/02/2024″;A:A;”<=30/6/2024″)
Nếu 01/02/2024 đặt ở ô J13, và 30/06/2024 đặt ở ô J14, thì công thức trên sửa thành:
=COUNTIFS(A:A;”>=”&J13;A:A;”<=”&J14)
Tiêu chí/Điều kiện đếm có dùng các toán tử:
Các toán tử được cho vào trong nháy kép.
> lớn hơn
=COUNTIF(A:A;”>01/02/2024″)
hoặc
=COUNTIF(A:A;”>”&J7)
>= lớn hơn hoặc bằng
=COUNTIF(A:A;”>=01/02/2024″)
hoặc
=COUNTIF(A:A;”>=”&J7)
< ít hơn
=COUNTIF(A:A;”<01/02/2024″)
hoặc
=COUNTIF(A:A;”<“&J7)
<= nhỏ hơn hoặc bằng
=COUNTIF(A:A;”<=01/02/2024″)
hoặc
=COUNTIF(A:A;”<=”&J7)
= bằng
=COUNTIF(A:A;”=01/02/2024″)
hoặc
=COUNTIF(A:A;”=”&J7)
<> không bằng
=COUNTIF(A:A;”<>01/02/2024″)
hoặc
=COUNTIF(A:A;”<>”&J7)
Tính toán với rỗng, ta dùng “”
=COUNTIF(C:C;””)
Tính toán với không rỗng, ta dùng “<>”
=COUNTIF(C:C;”<>”)
Tiêu chí/Điều kiện đếm với ký tự đặc biệt (đếm theo tiêu chí gần đúng)
Hàm COUNTIF và COUNTIFS sử dụng 3 ký tự: * ? và ~
Dấu sao * khớp với 0 hoặc nhiều ký tự
Đếm cả Trần Phú Thọ, Trần Quốc Hoàn, nhưng không đếm Nguyễn Trần Bảo An:
=COUNTIF(C:C;”Trần*”)
Đếm cả Trần Thị Lan, Thị Nở, Quả Thị
=COUNTIF(C:C;”*Thị*”)
Dấu chấm hỏi ? khớp với chính xác một ký tự, trường hợp dưới chỉ đếm Nữ, chứ không đếm Nam:
=COUNTIF(D:D;”N?”)
Nếu bạn dùng N* kết quả đếm Giới cả Nam và Nữ
=COUNTIF(D:D;”N?”)
Hoặc công thức dưới sẽ đếm với Phạm Thị A, Phạm Thị X, nhưng không đúng với Phạm Thị An, Phạm Thị Hoa Hồng:
=COUNTIF(C:C;”Phạm Thị ?”)
Nhưng nếu bạn dùng dấu ngã ~ là một ký tự thoát cho phép bạn tìm kiếm * hoặc ?, thay vì sử dụng chúng làm ký tự đại diện, thì khi đó:
=COUNTIF(C:C;”Phạm Thị ~?”)
sẽ đếm người có tên chính xác là Phạm Thị ? (ở đây ? được coi là tên, hay là ký tự thông thường, chứ không phải ký tự đại diện nữa)
Tương tự công thức dưới đây sẽ đếm Trần Phú Thọ, Trần Phú Quý:
=COUNTIF(C:C;”Trần Phú *”)
nhưng
=COUNTIF(C:C;”Trần Phú ~*”)
sẽ chỉ đếm người có tên chính xác là Trần Phú * (ở đây * được coi là tên, hay là ký tự thông thường, chứ không phải ký tự đại diện nữa)
COUNTIF và COUNTIFS với công thức mảng
Với hàm COUNTIF và COUNTIFS, ta lồng hàm INDEX phía ngoài (ngoài trừ với bảng đếm dạng Pivot), ví dụ:
=INDEX(COUNTIF(C:C;J2:J100))
COUNTIF và COUNTFS nhiều điều kiện
Bạn dùng COUNTIFS khi cộng nhiều điều kiện khác nhau, điều đó là hiển nhiên, nhưng bạn cũng hoàn toàn đếm theo nhiều điều kiện với hàm COUNTIF, cách dùng COUNTIF trong tình huống này sẽ giúp công thức của bạn trở lên gọn gàng hơn.
COUNTIF và COUNTIFS với bảng Pivot
Với bảng Pivot, thông thường nếu dùng COUNTIF ta sẽ kết hợp với INDEX và các điều kiện được ghép lại với nhau. Nếu dùng COUNTIFS, ta sẽ dùng kỹ thuật MAP hai chiều ngang dọc để làm. Hoặc ta có thể thay thế bằng các hàm FILTER và QUERY (xem thêm tại đây)
COUNTIF và COUNTIFS đếm lũy kế
COUNTIF và COUNTIFS để cộng dữ liệu lấy từ IMPORTRANGE
Bạn sẽ không thể sử dụng COUNTIF và COUNTIFS để cộng trực tiếp dữ liệu lấy từ IMPORTRANGE, khi đó sẽ báo lỗi:
Google Sheets có thể báo lỗi rằng “Đối số phải là 1 dải ô” (Argument must be a range) vì Google Sheets yêu cầu phạm vi trong hàm COUNTIF phải là một dải ô cụ thể, trong khi dữ liệu lấy từ IMPORTRANGE được coi là một mảng, chứ không phải một dải ô chuẩn.
Trong trình huống này, bạn phải sử dụng hàm LET, CHOOSECOLS để xác định các cột điều kiện cần đếm.
Tình huống thực hành
đang làm
