Hàm COUNTIF trong Excel cho phép bạn đếm số ô đáp ứng tiêu chí 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ự kết 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 ô phù hợp với tiêu chí 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 đáp ứng các tiêu chí 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 ô đáp ứng một tiêu chí trong một phạm vi, trong khi COUNTIFS đếm các ô đáp ứng nhiều điều kiện trong cùng một hoặc nhiều phạm vi.
Bài viết 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 chí 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 từng phần.
Cú pháp của hàm COUNTIF
Cấu trúc của một hàm COUNTIF là:
= COUNTIF (phạm vi, tiêu chí)Thông số:
phạm vi- Phạm vi ô cần đếm.tiêu chuẩn- Điều kiện xác định ô nào nên được đưa vào đếm trong phạm vi được chỉ định. Tiêu chí 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ư chúng ta đã thảo luận ở trên, tiêu chí (đối số thứ hai) trong hàm COUNTIF xác định đ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ị đáp ứng các điều kiện logic như bằng, lớn hơn, nhỏ hơn hoặc không bằng một giá trị được chỉ định, v.v.
Trong ví dụ dưới đây, công thức đếm các ô chứa giá trị bằng 5 (tiêu chí). 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 cứ 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 khi bạn muốn đếm các ô bằng cách kiểm tra chúng dựa trên một tiêu chí (giá trị) trong một ô. Trong những trường hợp như vậy, hãy tạo một tiêu chí bằng cách kết 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 (“”), sau đó đặt dấu và (&) giữa toán tử so sánh và tham chiếu ô.
= COUNTIF (B2: B11, "> =" & D2)
Hình ả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 chí hoặc ô chứa chuỗi văn bản. Ví dụ: trong bảng dưới đây, nếu chúng ta muốn đếm tất cả các ô trong phạm vi (B21: D27) có giá trị văn bản trong ô B21 (sam) trong đó, chúng ta có thể sử dụng công thức sau:
= COUNTIF (B21: D27, B21)
Như chúng ta đã thảo luận trước đây, 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 chí (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 bao gồm 'không bằng' "" 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 phải đặt toán tử ‘’ và chuỗi văn bản lại với 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 (Khớp một phần)
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 bắt đầu và kết thúc. (ví dụ: St * có thể có nghĩa là Stark, Stork, Stacks, v.v.?(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ố ô bắt đầu hoặc kết thúc với một số ký tự nhất định
Để đếm các ô bắt đầ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 ô bắt đầ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ỳ vị trí nào trong chuỗi văn bản.
MỘT ? đại diện cho chính xác 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 chính xác ký tự +1 trong đó ‘?' Được sử dụng.
= COUNTIF (A1: A10, "Mệnh giá")
Đế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 bên dưới. Công thức này coi các ô có ngày và số là ô trống và sẽ không đưa chúng vào số lượng.
= COUNTIF (A1: B12, "*")Ký tự đại diện * chỉ khớp với các giá trị văn bản và trả về tổng số tất cả các giá trị văn bản trong phạm vi đã cho.
Nếu bạn muốn đếm tất cả 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 chí để đế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, "" & "*")Từ * ký tự đại diện khớp với bất kỳ giá trị văn bản nào, công thức trên sẽ đếm tất cả các ô không bằng *. Nó cũng đếm các ô có ngày và số dưới dạng ô trống.
Để đếm tất cả các ô trống (tất cả 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 (giống như bạn đã làm với tiêu chí số) đáp ứng điều kiện lôgic hoặc ngày hoặc tháng đượ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 chí 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 trước 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 lớn 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 kết 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 trước ngày trong E3, hãy sử dụng công thức dưới đây, trong đó toán tử lớn 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ể kết 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 tất cả 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 tất cả các ngày giữa hai ngày, bạn cần sử dụng hai tiêu chí trong công thứ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 tất cả các ngày giữa hai ngày được chỉ định.
Để đếm các ngày trong khoảng 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 đầu 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. Bây giờ chúng ta nhận đượ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 chí thứ hai.
Sử dụng hàm COUNTIFS trong Excel
Hàm COUNTIFS cũng hỗ trợ nhiều tiêu chí và không giống như hàm COUNTIF, hàm này chỉ đếm các ô sau khi tất cả các điều kiện được đáp ứng. Nếu bạn muốn đếm các ô có tất cả 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 đã 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 chí thay vì tham chiếu ô cho ví dụ này.
Cách xử lý COUNTIF và COUNTIFS với nhiều tiêu chí trong Excel
Hàm COUNTIF chủ yếu được sử dụng để đếm các ô có tiêu chí 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 ô phù 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 ô đáp ứng 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 phổ biến cho hàm COUNTIF với nhiều tiêu chí là đếm các số giữa hai số được chỉ định, ví dụ: để đếm các số lớn hơn 10 nhưng nhỏ hơn 50. Để đếm các số trong một phạm vi, hãy kết hợp hai hoặc nhiều hàm COUNTIF với nhau trong một công thức. Hãy để chúng tôi chỉ cho bạn cách làm.
Giả sử bạn muốn đếm các ô trong phạm vi B2: B9 trong đó giá trị lớn 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 đầu tiên đếm các số lớn hơn 10 (là 7), công thức thứ hai trả về số đếm lớn 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 đầu tiên (7 -4) để lấy số đếm giữa hai số (3).
Nếu bạn muốn đếm các ô có số lớn 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 này:
= 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 này:
= 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 chí (AND Criteria)
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 chí trong cùng một hoặc nhiều phạm vi. Nó được gọi là 'logic AND' vì hàm được tạo ra để đếm ô chỉ khi tất cả các điều kiện đã cho là ĐÚNG.
Ví dụ: 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 nhỏ hơn 5 (giá trị trong cột C).
Chúng ta có thể sử dụng công thức này:
= COUNTIFS (A2: A14, "Bánh mì", C2: C14, "<5")
COUNTIF để đếm ô có nhiều tiêu chí (HOẶC tiêu chí)
Nếu bạn muốn đếm số ô đáp ứng nhiều tiêu chí 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, "Bánh mì") + COUNTIF (A2: A14, "Phô mai")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à lý do tại sao nó được gọi là 'logic HOẶC'.
Nếu bạn muốn đánh giá nhiều hơn một tiêu chí 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, "Bánh mì", C2: C14, "Đã đặt hàng") + COUNTIFS (A2: A14, "Bánh mì", C2: C14, "Đã giao hàng")
Chúng tôi hy vọng điều này dễ dàng, nhưng một hướng dẫn dài sẽ cung cấp cho bạn một số ý tưởng về cách sử dụng các hàm COUNTIF và COUNTIF trong Excel.