Cách sử dụng hàm COUNTIFS trong Excel -

Mục lục

Related videos

Tóm lược

Hàm COUNTIFS trong Excel trả về số lượng ô đáp ứng một hoặc nhiều tiêu chí. COUNTIFS có thể được sử dụng với tiêu chí dựa trên ngày, số, văn bản và các điều kiện khác. COUNTIFS hỗ trợ các toán tử logic (>, <,, =) và các ký tự đại diện (*,?) Để đối sánh từng phần.

Mục đích

Đếm các ô phù hợp với nhiều tiêu chí

Giá trị trả lại

Số lần tiêu chí được đáp ứng

Cú pháp

= COUNTIFS (phạm vi1, tiêu chí1, (phạm vi2), (tiêu chí2),…)

Tranh luận

range1 - Phạm vi đầu tiên cần tránh.
tiêu chí1 - Tiêu chí sử dụng trên phạm vi1.
range2 - (tùy chọn) Phạm vi thứ hai để đánh giá.
tiêu chí2 - (tùy chọn) Tiêu chí sử dụng trên phạm vi2.

Phiên bản

Excel 2007

Ghi chú sử dụng

Hàm COUNTIFS trong Excel đếm số ô trong phạm vi khớp với một tiêu chí được cung cấp. Không giống như hàm COUNTIF cũ hơn, COUNTIFS có thể áp dụng nhiều điều kiện cùng một lúc. Các điều kiện được cung cấp với các cặp phạm vi / tiêu chí và chỉ cặp đầu tiên là bắt buộc. Đối với mỗi điều kiện bổ sung, bạn phải cung cấp một cặp phạm vi / tiêu chí khác. Cho phép tối đa 127 cặp phạm vi / tiêu chí.

Tiêu chí có thể bao gồm toán tử logic (>, <,, =) và ký tự đại diện (*,?) Để đối sánh từng phần. Tiêu chí cũng có thể dựa trên một giá trị từ một ô khác, như được giải thích bên dưới.

COUNTIFS nằm trong nhóm tám hàm trong Excel chia các tiêu chí logic thành hai phần (phạm vi + tiêu chí). Do đó, cú pháp được sử dụng để xây dựng tiêu chí khác nhau và COUNTIFS yêu cầu một phạm vi ô cho các đối số phạm vi, bạn không thể sử dụng một mảng.

Ví dụ cơ bản

Với ví dụ được hiển thị, COUNTIFS có thể được sử dụng để đếm các bản ghi sử dụng 2 tiêu chí như sau:

=COUNTIFS(C5:C14,"red",D5:D14,"tx") // red and TX =COUNTIFS(C5:C14,"red",F5:F14,">20") // red and>20

Lưu ý rằng hàm COUNTIFS không phân biệt chữ hoa chữ thường.

Dấu ngoặc kép ("") trong tiêu chí

Nói chung, giá trị văn bản cần được đặt trong dấu ngoặc kép và số thì không. Tuy nhiên, khi toán tử logic được bao gồm với một số, thì số và toán tử phải được đặt trong dấu ngoặc kép như được hiển thị bên dưới:

=COUNTIFS(A1:A10,100) // count equal to 100 =COUNTIFS(A1:A10,">50") // count greater than 50 =COUNTIFS(A1:A10,"jim") // count equal to "jim"

Lưu ý: chỉ hiển thị một điều kiện để đơn giản. Các điều kiện bổ sung phải tuân theo các quy tắc tương tự.

Giá trị từ một ô khác

Khi sử dụng một giá trị từ một ô khác trong một điều kiện, tham chiếu ô phải được nối với một toán tử khi được sử dụng. Trong ví dụ dưới đây, COUNTIFS sẽ đếm các giá trị trong A1: A10 nhỏ hơn giá trị trong ô B1. Lưu ý rằng toán tử less than (là văn bản) được đặt trong dấu ngoặc kép, nhưng tham chiếu ô không phải là:

=COUNTIFS(A1:A10,"<"&B1) // count cells less than B1

Lưu ý: COUNTIFS là một trong một số hàm chia điều kiện thành hai phần: phạm vi + tiêu chí. Điều này gây ra một số mâu thuẫn đối với các công thức và hàm khác.

Không bằng

Để xây dựng tiêu chí "không bằng", hãy sử dụng toán tử "" được bao quanh bởi dấu ngoặc kép (""). Ví dụ: công thức bên dưới sẽ đếm các ô không bằng "đỏ" trong phạm vi A1: A10:

=COUNTIFS(A1:A10,"red") // not "red"

Ô trống

COUNTIFS có thể đếm các ô trống hoặc không trống. Các công thức bên dưới đếm các ô trống và không phải ô trống trong phạm vi A1: A10:

=COUNTIFS(A1:A10,"") // not blank =COUNTIFS(A1:A10,"") // blank

ngày

Cách dễ nhất để sử dụng COUNTIFS với ngày là tham chiếu đến ngày hợp lệ trong một ô khác có tham chiếu ô. Ví dụ: để đếm các ô trong A1: A10 có chứa một ngày lớn hơn một ngày trong B1, bạn có thể sử dụng công thức như sau:

=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1

Lưu ý rằng chúng tôi nối toán tử ">" với ngày tháng trong B1, nhưng không có dấu ngoặc kép xung quanh tham chiếu ô.

Cách an toàn nhất để mã hóa một ngày thành COUNTIFS là với hàm DATE. Điều này đảm bảo Excel sẽ hiểu ngày tháng. Để đếm các ô trong A1: A10 có ngày nhỏ hơn ngày 1 tháng 9 năm 2020, bạn có thể sử dụng:

=COUNTIFS(A1:A10,"<"&DATE(2020,9,1)) // dates less than 1-Sep-2020

Ký tự đại diện

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character, and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in a A1:A5 that contain the text "apple" anywhere, you can use a formula like this:

=COUNTIFS(A1:A5,"*apple*") // count cells that contain "apple"

The tilde (~) is an escape character to allow you to find literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

Notes

Multiple conditions are applied with AND logic, i.e. condition 1 AND condition 2, etc.
Each additional range must have the same number of rows and columns as range1, but ranges do not need to be adjacent. If you supply ranges that don't match, you'll get a #VALUE error.
Non-numeric criteria needs to be enclosed in double quotes but numeric criteria does not. For example: 100, "100", ">32", "jim", or A1 (where A1 contains a number).
The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters.
To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).