Table of Contents
Cách sử dụng hàm COUNTIF trong Excel cơ bản đến nâng cao
Hàm COUNTIF trong Excel cho phép bạn đếm số ô thỏa mãn tiêu chuẩn hoặc điều kiện cụ thể trong phạm vi nhất định.
Hàm COUNTIF là một trong những hàm thống kê trong Excel, là sự phối hợp của các hàm COUNT và IF hoặc hàm COUNTA. Khi được sử dụng trong fomula, hàm đếm số lượng ô thích hợp với tiêu chuẩn hoặc điều kiện cụ thể trong cùng một hoặc nhiều phạm vi. Hàm COUNTIF giúp đếm các ô chứa văn bản, số hoặc ngày thỏa mãn các tiêu chuẩn cụ thể.
Bạn có thể đếm ô bằng cách sử dụng hàm COUNTIF hoặc COUNTIFS trong Excel. Sự khác biệt giữa hàm COUNTIF và COUNTIFS là COUNTIF được sử dụng để đếm các ô thỏa mãn một tiêu chuẩn trong một phạm vi, trong khi COUNTIFS đếm các ô thỏa mãn nhiều điều kiện trong cùng một hoặc nhiều phạm vi.
Nội dung này sẽ hướng dẫn bạn cách sử dụng hai hàm COUNTIF và COUNTIFS trong Excel.
Hàm COUNTIF trong Excel
Hàm COUNTIF cho phép bạn thực hiện đếm dữ liệu dựa trên một tiêu chuẩn hoặc điều kiện cụ thể. Điều kiện được sử dụng trong hàm hoạt động với các toán tử logic (<,>, <>, =,> =, <=) và các ký tự đại diện (*,?) Để đối sánh một phần.
Cú pháp của hàm COUNTIF
Cấu trúc của một hàm COUNTIF là:
=COUNTIF(range,criteria)Thông số kỹ thuật:
range– Phạm vi ô cần đếm.criteria– Điều kiện xác nhận ô nào nên được mang vào đếm trong phạm vi được chỉ định. Tiêu chuẩn có thể là giá trị số, văn bản, tham chiếu đến địa chỉ ô hoặc phương trình.
Sử dụng hàm COUNTIF để đếm giá trị số
Như tất cả chúng ta đã thảo luận ở trên, tiêu chuẩn (đối số thứ hai) trong hàm COUNTIF xác nhận điều kiện cho hàm biết những ô nào cần đếm.
Hàm này giúp bạn đếm số lượng ô có giá trị thỏa mãn các điều kiện logic như bằng, to hơn, nhỏ hơn hoặc không bằng một giá trị được chỉ định,…
Trong ví dụ dưới đây, công thức đếm các ô chứa giá trị bằng 5 (tiêu chuẩn). Bạn có thể chèn trực tiếp ‘5 vào công thức hoặc sử dụng tham chiếu đến địa chỉ ô có giá trị (ô D2 trong ví dụ bên dưới).
=COUNTIF(B2:B11,D2)
Công thức trên đếm số ô trong phạm vi ô (B2: B11) có chứa giá trị bằng giá trị trong ô D2.
Công thức sau đây đếm các ô có giá trị nhỏ hơn 5.
=COUNTIF(B2:B11,"<5")Toán tử less than (<) cho công thức đếm các ô có giá trị nhỏ hơn ‘5’ trong phạm vi B2: B11. Bất kì khi nào bạn sử dụng một toán tử trong điều kiện, hãy đảm bảo đặt nó trong dấu ngoặc kép (“”).
Đôi lúc bạn muốn đếm các ô bằng cách xác minh chúng dựa trên một tiêu chuẩn (giá trị) trong một ô. Trong những trường hợp như vậy, hãy tạo một tiêu chuẩn bằng cách phối hợp một toán tử và một tham chiếu ô. Khi làm điều đó, bạn cần đặt toán tử so sánh trong dấu ngoặc kép (“”), rồi đặt dấu và (&) giữa toán tử so sánh và tham chiếu ô.
=COUNTIF(B2:B11,">="&D2)
Hình dưới đây cho thấy một vài công thức ví dụ và kết quả của chúng.
Sử dụng hàm COUNTIF để đếm giá trị văn bản
Để đếm các ô chứa chuỗi văn bản nhất định, hãy sử dụng chuỗi văn bản đó làm đối số tiêu chuẩn hoặc ô chứa chuỗi văn bản. Ví dụ, trong bảng dưới đây, nếu tất cả chúng ta muốn đếm toàn bộ các ô trong phạm vi (B21: D27) có giá trị văn bản trong ô B21 (sam) trong đó, tất cả chúng ta có thể sử dụng công thức sau:
=COUNTIF(B21:D27,B21)
Như tất cả chúng ta đã thảo luận trước đó, tất cả chúng ta có thể sử dụng văn bản ‘sam’ trực tiếp trong công thức hoặc sử dụng tham chiếu ô có tiêu chuẩn (B21). Một chuỗi văn bản phải luôn được đặt trong dấu ngoặc kép (“”) khi nó được sử dụng trong một công thức trong excel.
=COUNTIF(B21:D27,"sam")
Để đếm các ô không chứa văn bản được chỉ định, hãy sử dụng công thức dưới đây:
=COUNTIF(B21:D27,"<>"&B21)Đảm bảo đặt toán tử "<>"trong dấu ngoặc kép.
Nếu bạn đang sử dụng văn bản ‘sam’ trực tiếp trong công thức, bạn cần đặt toán tử ‘<>’ và chuỗi văn bản cùng nhau ( "<>sam") trong dấu ngoặc kép.
=COUNTIF(B21:D27,"<>sam") 
Sử dụng ký tự đại diện trong hàm COUNTIF trong Excel
Bạn có thể sử dụng công thức COUNTIF với các ký tự đại diện để đếm các ô chứa một từ, cụm từ hoặc chữ cái cụ thể. Có ba ký tự đại diện bạn có thể sử dụng trong hàm COUNTIF của Excel:
*(dấu hoa thị) – Nó được sử dụng để đếm các ô có bất kỳ số ký tự / chữ cái khởi đầu và kết thúc. Ví dụ: St * có thể có nghĩa là Stark, Stork, Stacks,…?(dấu chấm hỏi) – Nó được sử dụng để tìm các ô có bất kỳ ký tự đơn nào. Ví dụ: St? rk có thể có nghĩa là Stark hoặc Stork.~(dấu ngã) – Nó được sử dụng để tìm và đếm số lượng ô có chứa dấu chấm hỏi hoặc ký tự dấu sao (~, *,?) trong văn bản.
Đếm số ô khởi đầu hoặc kết thúc với các ký tự nhất định
Để đếm các ô khởi đầu hoặc kết thúc bằng văn bản cụ thể với bất kỳ số ký tự nào khác trong ô, hãy sử dụng ký tự đại diện dấu hoa thị (*) trong đối số thứ hai của hàm COUNTIF.
Sử dụng công thức mẫu sau:
=COUNTIF(A1:A10,"A*") – để đếm các ô khởi đầu bằng “A”.
=COUNTIF(A19:A28,"*er") – để đếm số ô kết thúc bằng ký tự “er”.
=COUNTIF(A2:A12,"*QLD*") – để đếm các ô có chứa văn bản “QLD” ở bất kỳ đâu trong chuỗi văn bản.
Kí tự ‘?’ đại diện cho đúng đắn một ký tự, hãy sử dụng ký tự đại diện này trong hàm COUNTIF bên dưới để đếm số ô chứa đúng đắn ký tự +1 mà ‘ ?‘ được sử dụng.
=COUNTIF(A1:A10,"Par?s")
Đếm các ô trống và không trống với hàm COUNTIF
Công thức COUNTIF cũng hữu ích khi đếm số lượng ô trống hoặc không trống trong một phạm vi nhất định.
Đếm ô không trống
Nếu bạn chỉ muốn đếm các ô chứa bất kỳ giá trị ‘văn bản’ nào, hãy sử dụng công thức dưới đây. Công thức này coi các ô có ngày và số là ô trống và sẽ không mang chúng vào số lượng.
=COUNTIF(A1:B12,"*")Ký tự đại diện ‘*‘ khớp với các giá trị văn bản và trả về tổng số toàn bộ các giá trị văn bản trong phạm vi đã cho.
Nếu bạn muốn đếm toàn bộ các ô không trống trong một phạm vi nhất định, hãy thử công thức sau:
=COUNTIF(A1:B12,"<>")
Đếm ô trống
Nếu bạn muốn đếm các ô trống trong một phạm vi nhất định, hãy sử dụng hàm COUNTIF với ‘*'(ký tự đại diện) và ‘<>‘ (toán tử trong đối số tiêu chuẩn) để đếm các ô trống.
Công thức này đếm các ô không chứa bất kỳ giá trị văn bản nào:
=COUNTIF(A1:B12,"<>"&"*")Nó cũng đếm các ô có ngày và số dưới dạng ô trống.
Để đếm toàn bộ các khoảng trống (toàn bộ các loại giá trị):
=COUNTIF(A1:B12,"")Hàm này chỉ đếm các ô trống trong phạm vi.
Sử dụng hàm COUNTIF để đếm ngày
Bạn có thể đếm các ô có ngày tháng (giống như bạn đã làm với tiêu chuẩn số) thỏa mãn điều kiện lôgic hoặc ngày tháng năm được chỉ định trong ô tham chiếu.
Để đếm các ô có chứa ngày được chỉ định (05-05-2020), chúng tôi sẽ sử dụng công thức sau:
=COUNTIF(B2:B10,"05-05-2020")
Bạn cũng có thể chỉ định một ngày ở các định dạng khác nhau làm tiêu chuẩn trong hàm COUNTIF như được hiển thị bên dưới:
Nếu bạn muốn đếm các ô chứa ngày xưa hoặc sau một ngày nhất định, hãy sử dụng toán tử nhỏ hơn (trước) hoặc to hơn (sau) cùng với ngày cụ thể hoặc tham chiếu ô.
=COUNTIF(B2:B10,">=05/05/2020")
Bạn cũng có thể sử dụng tham chiếu ô có chứa ngày tháng bằng cách phối hợp nó với toán tử (trong dấu ngoặc kép).
Để đếm số ô trong phạm vi A2: A14 có ngày xưa ngày trong E3, hãy sử dụng công thức dưới đây, trong đó toán tử to hơn (<) có nghĩa là trước ngày trong E3.
=COUNTIF(A2:A14,"<"&E3)
Một vài công thức ví dụ và kết quả của chúng:
Đếm ngày dựa trên ngày hiện tại
Bạn có thể phối hợp hàm COUNTIF với các hàm Ngày cụ thể của Excel, tức là TODAY () để đếm các ô có ngày hiện tại.
=COUNTIF(A2:A14,">"&TODAY())Hàm này đếm toàn bộ các ngày kể từ hôm nay trong phạm vi (A2: A14).
Đếm ngày giữa một phạm vi ngày cụ thể
Nếu bạn muốn đếm toàn bộ các ngày giữa hai ngày, bạn cần sử dụng hai tiêu chuẩn trong công thức.
Tất cả chúng ta có thể làm điều này bằng cách sử dụng hai phương pháp: hàm COUNTIF và COUNTIFS.
Sử dụng hàm COUNTIF trong Excel
Bạn cần sử dụng hai hàm COUNTIF để đếm toàn bộ các ngày giữa hai ngày được chỉ định.
Để đếm các ngày từ ’09 -02-2020 ′ đến ’20 -08-2021 ′, hãy sử dụng công thức sau:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">20-08-2021")Công thức này trước tiên tìm số ô có ngày sau ngày 2 tháng 2 và trừ số ô có ngày sau ngày 20 tháng 8. Lúc này tất cả chúng ta thu được số không. trong số các ô có ngày đến sau ngày 2 tháng 2 và vào hoặc trước ngày 20 tháng 8 (số lượng là 9).
Nếu bạn không muốn công thức tính cả ngày 2 tháng 2 và ngày 20 tháng 8, hãy sử dụng công thức này để thay thế:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">=20-08-2021")Chỉ cần thay toán tử ‘>’ bằng ‘> =’ trong tiêu chuẩn thứ hai.
Sử dụng hàm COUNTIFS trong Excel
Hàm COUNTIFS cũng trợ giúp nhiều tiêu chuẩn và không giống như hàm COUNTIF, hàm này chỉ đếm các ô sau thời điểm toàn bộ các điều kiện được thỏa mãn. Nếu bạn muốn đếm các ô có toàn bộ các ngày giữa hai ngày được chỉ định, hãy nhập công thức này:
=COUNTIFS(A2:A14,">"&A11,A2:A14,"<"&A10)
Nếu bạn muốn bao gồm cả các ngày được chỉ định trong số lượng, hãy sử dụng toán tử ‘> =’ và ‘<=’. Ở đây, hãy làm theo công thức này:
=COUNTIFS(A2:A14,">=09-02-2020",A2:A14,"<=20-08-2021")Chúng tôi đã sử dụng ngày trực tiếp trong tiêu chuẩn thay vì tham chiếu ô cho ví dụ này.
Cách xử lý COUNTIF và COUNTIFS với nhiều tiêu chuẩn trong Excel
Hàm COUNTIF hầu hết được sử dụng để đếm các ô có tiêu chuẩn duy nhất (điều kiện) trong một phạm vi. Nhưng bạn vẫn có thể sử dụng COUNTIF để đếm các ô thích hợp với nhiều điều kiện trong cùng một phạm vi. Tuy nhiên, hàm COUNTIFS có thể được sử dụng để đếm các ô thỏa mãn nhiều điều kiện trong các phạm vi giống nhau hoặc khác nhau.
Cách đếm số trong một phạm vi
Bạn có thể đếm các ô chứa số giữa hai số được chỉ định bằng cách sử dụng hai hàm: COUNTIF và COUNTIFS.
COUNTIF để đếm số giữa hai số
Một trong những cách sử dụng thông dụng cho hàm COUNTIF với nhiều tiêu chuẩn là đếm các số giữa hai số được chỉ định. Ví dụ: để đếm các số to hơn 10 nhưng nhỏ hơn 50. Để đếm các số trong một phạm vi, hãy phối hợp hai hoặc nhiều hàm COUNTIF với nhau trong một công thức.
Giả sử bạn muốn đếm các ô trong phạm vi B2: B9 trong đó giá trị to hơn 10 và nhỏ hơn 21 (không bao gồm 10 và 21), hãy làm theo công thức sau:
=COUNTIF(B2:B14,">10")-COUNTIF(B2:B14,">=21")Sự khác biệt giữa hai số được tìm thấy bằng cách trừ một công thức này với một công thức khác. Công thức trước tiên đếm các số to hơn 10 (là 7), công thức thứ hai trả về số lượng các số to hơn hoặc bằng 21 (là 4) và kết quả của công thức thứ hai được trừ khỏi công thức trước tiên (7 -4) để lấy số đếm giữa hai số (3).
Nếu bạn muốn đếm các ô có số to hơn 10 và nhỏ hơn 21 trong phạm vi B2: B14, bao gồm cả số 10 và 21, hãy sử dụng công thức sau:
=COUNTIF(B2:B14,">=10")-COUNTIF(B2:B14,">21")
COUNTIFS để đếm số giữa 2 số
Để đếm các số từ 10 đến 21 (không bao gồm 10 và 21) được chứa trong các ô từ B2 đến B9, hãy sử dụng công thức sau:
=COUNTIFS(B2:B14,">10",B2:B14,"<21")
Để bao gồm 10 và 21 trong số đếm, chỉ cần sử dụng các toán tử ‘lớn hơn hoặc bằng’ (> =) thay vì ‘lớn hơn’ và ‘nhỏ hơn hoặc bằng’ (<=) thay vì các toán tử ‘nhỏ hơn’ trong công thức .
COUNTIFS để đếm ô có nhiều tiêu chuẩn (AND)
Hàm COUNTIFS là đối số nhiều của hàm COUNTIF đếm ô dựa trên hai hoặc nhiều tiêu chuẩn trong cùng một hoặc nhiều phạm vi. Nó được gọi là hàm logic ” AND”, bởi vì hàm được tạo ra để đếm ô chỉ khi toàn bộ các điều kiện đã cho rằng ĐÚNG.
Ví dụ: tất cả chúng ta muốn tìm xem có bao nhiêu lần (số lượng ô) mà bánh mì (giá trị trong cột A) đã được bán ít hơn 5 (giá trị trong cột C) bao nhiêu lần.
Tất cả chúng ta có thể sử dụng công thức này:
=COUNTIFS(A2:A14,"Bread",C2:C14,"<5")
COUNTIF để đếm ô có nhiều tiêu chuẩn
Nếu bạn muốn đếm số ô thỏa mãn nhiều tiêu chuẩn trong cùng một phạm vi, hãy nối hai hoặc nhiều hàm COUNTIF với nhau. Ví dụ: nếu bạn muốn tìm hiểu số lần “Bánh mì” hoặc “Phô mai” được lặp lại trong phạm vi được chỉ định (A2: A14), hãy sử dụng công thức dưới đây:
=COUNTIF(A2:A14,"Bread")+COUNTIF(A2:A14,"Cheese")Công thức này đếm các ô có ít nhất một trong các điều kiện là ĐÚNG. Đó là nguyên nhân vì sao nó được gọi là hàm logic “Or”
Nếu bạn muốn nhận xét nhiều hơn một tiêu chuẩn trong mỗi hàm, tốt hơn nên sử dụng COUNTIFS thay vì COUNTIF. Trong ví dụ bên dưới, chúng tôi muốn tính số lượng trạng thái “Đã đặt hàng” và “Đã giao” cho ‘Bánh mì’, vì vậy chúng tôi sẽ sử dụng công thức này:
=COUNTIFS(A2:A14,"Bread",C2:C14,"Ordered")+COUNTIFS(A2:A14,"Bread",C2:C14,"Delivered")
Chúng tôi kì vọng những hướng dẫn trên sẽ phân phối cho bạn một số ý tưởng về cách sử dụng các hàm COUNTIF và COUNTIF trong Excel.
