Hàm COUNTIFS, cách dùng hàm đếm ô theo nhiều điều kiện trong Excel

Hàm COUNTIFS trong Excel có rất nhiều ứng dụng thực tế. Dưới đây là những điều bạn cần biết về cách dùng hàm COUNTIFS trong Excel.

Microsoft Excel là phần mềm bảng tính nổi tiếng trên thế giới với rất nhiều tính năng hữu ích. Sự ra đời của phần mềm giúp công việc nhập liệu và tính toán hàng loạt con số trở nên đơn giản hơn bao giờ hết. Và khi dùng Excel, nhất định bạn cần biết tới hàm COUNTIFS.

Hàm COUNTIFS trong Excel dùng để đếm ô thỏa mãn nhiều điều kiện cho trước. Hàm COUNTIFS là một trong những hàm Excel thống kê được sử dụng nhiều trong Excel, là hàm nâng cao của hàm COUNTIF chỉ đếm ô với 1 điều kiện cho trước mà thôi. Khi thực hiện với hàm COUNTIFS, người dùng dễ dàng tìm ra ô kết quả thỏa mãn các điều kiện được đưa ra trong yêu cầu. Các điều kiện có thể là số, ngày tháng, text hay ô chứa dữ liệu. Bài viết dưới đây sẽ hướng dẫn bạn đọc cách sử dụng hàm COUNTIFS trong Excel.

• Cách so sánh dữ liệu trên 2 cột Excel
• Cách dùng hàm DSUM để tính tổng với điều kiện phức tạp trong Excel
• Cách sử dụng hàm SUMIF để tính tổng có điều kiện trong Excel
• Hàm VLOOKUP cách sử dụng và ví dụ cụ thể

Hướng dẫn dùng hàm COUNTIFS trong Excel

Cú pháp hàm COUNTIFS là =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…).

Trong đó:

• Criteria_range1 là vùng chọn đầu tiên cần thống kê, giá trị bắt buộc.
• Criteria1 là điều kiện áp dụng cho vùng chọn criteria_range1, giá trị bắt buộc có thể là ô, biểu thức, văn bản.
• [criteria_range2, criteria2] là các cặp vùng chọn và điều kiện bổ sung, cho phép tối đa 127 cặp vùng chọn và điều kiện.

Lưu ý khi sử dụng hàm COUNTIFS:

• Các vùng chọn bổ sung cần phải có cùng số hàng và cột với vùng criteria_range1, có thể tách rời nhau.
• Điều kiện vùng chọn tham chiếu đến ô trống thì hàm COUNTIFS tự động coi giá trị là 0.
• Có thể dùng các ký tự ? để thay thế cho ký tự nào đó, dấu * thay thế cho cả một chuỗi ký tự. Nếu cần tìm dấu ? hay dấu * thực thì gõ dấu ~ ở trước ký tự đó.
• Lưu ý rằng hàm COUNTIFS không phân biệt chữ hoa chữ thường.
• Nói chung, các giá trị văn bản cần được đặt trong dấu ngoặc kép, còn số thì không. Tuy nhiên, khi một toán tử logic được bao gồm với một số, 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 ý: Các điều kiện bổ sung phải tuân theo các quy tắc tương tự.

• 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 ô thì không cần:

=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.

• COUNTIFS có thể đếm các ô trống hoặc không trống. Các công thức dưới đây đế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

• Cách dễ nhất để sử dụng COUNTIFS với ngày là tham chiếu đến một 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

Ví dụ về cách sử dụng hàm COUNTIFS trên Excel

1. Bảng dữ liệu số 1

Chúng ta có bảng dữ liệu dưới đây để thực hiện một số yêu cầu cho bảng.

Ví dụ 1: Tính số lượng nhân viên Nam có 25 ngày công làm việc.

Tại ô cần nhập kết quả chúng ta nhập công thức =COUNTIFS(C2:C7,"Nam",D2:D7,25) rồi nhấn Enter.

Trong đó:

• C2:C7 là vùng đếm 1 bắt buộc với Giới tính của nhân viên.
• Nam là điều kiện của vùng đếm 1.
• C2:C7 là vùng đếm 2 với Ngày công của nhân viên.
• 25 là điều kiện của vùng đếm 2.

Kết quả chúng ta có 2 nhân viên là Nam có 25 ngày công.

Ví dụ 2: Tính số nhân viên Nam có ngày nghỉ là 0.

Tại ô nhập công thức chúng ta nhập =COUNTIFS(C2:C7,"Nam",E2:E7,0) rồi nhấn Enter.

Kết quả hiển thị 1 nhân viên Nam có 0 ngày nghỉ.

Ví dụ 3: Tính số nhân viên Nam có ngày công 25 ngày và có số ngày nghỉ ít hơn hoặc bằng 2 ngày.

Chúng ta nhập công thức =COUNTIFS(C2:C7,"Nam",D2:D7,25,E2:E7,"<=2") rồi nhấn Enter.

Kết quả có 2 nhân viên Nam đáp ứng đủ yêu cầu có ngày nghỉ ít hơn hoặc bằng 2 ngày.

2. Bảng dữ liệu số 2

Ví dụ 1: Tính tổng các mặt hàng Đài Loan có đơn giá bán dưới 200,000đ.

Tại ô nhập kết quả chúng ta nhập công thức tính =COUNTIFS(B2:B7,"*Đài Loan",C2:C7,"<200,000") rồi nhấn Enter.

Kết quả được 2 mặt hàng Đài Loan đúng với yêu cầu.

Ví dụ 2: Tính tổng sản phẩm mặt hàng Đài Loan có đơn giá trong khoảng 100,000đ đến 150,000đ.

Tại ô nhập kết quả chúng ta nhập công thức =COUNTIFS(B2:B7,"*Đài Loan",C2:C7,">100000",C2:C7,"<150000") rồi nhấn Enter.

Kết quả sẽ có 2 sản phẩm Đài Loan có giá trong khoảng 100,000đ – 150,000đ.

Cách sử dụng hàm COUNTIFS kết hợp SUM trong Excel

Hàm COUNTIFS về cơ bản là một phiên bản phức tạp hơn của hàm COUNTIF. Ưu điểm chính mà COUNTIFS vượt trội hơn so với COUNTIF là nó hỗ trợ nhiều điều kiện và phạm vi.

Tuy nhiên, bạn cũng có thể xác định một phạm vi và một điều kiện duy nhất cho hàm COUNTIFS, tương tự như đã làm với hàm COUNTIF.

Một điều quan trọng mà bạn nên hiểu về hàm COUNTIFS trước khi sử dụng là hàm này không chỉ đơn giản là tính tổng kết quả của các ô đáp ứng tiêu chí cho mỗi phạm vi ô.

Trong thực tế, nếu bạn có hai điều kiện cho hai phạm vi, các ô trong phạm vi đầu tiên được lọc hai lần: Một lần qua điều kiện đầu tiên và sau đó qua điều kiện thứ hai. Điều này có nghĩa là hàm COUTNIFS sẽ chỉ trả về các giá trị đáp ứng cả hai điều kiện, trong phạm vi đã cho của chúng.

Bạn có thể hiểu rõ hơn về chức năng của hàm COUNTIFS bằng cách nghiên cứu ví dụ bên dưới.

Ngoài màu sắc của táo, còn có một cột mô tả kích thước của chúng. Mục tiêu cuối cùng trong ví dụ này là đếm số lượng táo đỏ cỡ lớn.

1. Chọn ô mà bạn muốn hiển thị kết quả. (Trong ví dụ này, bài viết sẽ hiển thị số lượng táo đỏ lớn trong ô E3).

2. Đi tới thanh công thức và nhập công thức bên dưới:

=COUNTIFS(A3:A11, "Red", B3:B11, "Big")

3. Với lệnh này, công thức kiểm tra các ô từ A3 đến A11 cho điều kiện “Red”. Các ô vượt qua vòng kiểm tra này sau đó được kiểm tra thêm lần nữa trong phạm vi từ B3 đến B11 cho điều kiện “Big”.

4. Nhấn nút Enter.

5. Bây giờ, Excel sẽ đếm số lượng táo đỏ cỡ lớn.

Hãy quan sát cách công thức đếm các ô có cả thuộc tính red và big. Công thức lấy các ô từ A3 đến A11 và kiểm tra chúng để tìm kết quả red. Các ô vượt qua điều kiện này, sau đó lại được kiểm tra với điều kiện tiếp theo trong phạm vi thứ hai, trong trường hợp này, là điều kiện big.

Kết luận, các phạm vi và điều kiện sau phạm vi và điều kiện đầu tiên ngày càng thu hẹp bộ lọc đếm và không độc lập với nhau. Vì vậy, kết quả cuối cùng của công thức là những quả táo có màu đỏ (red) và cỡ to (big). Bạn có thể đếm số lượng táo đỏ hoặc cỡ lớn bằng cách kết hợp hàm COUNTIF với hàm SUM.

1. Chọn ô mà bạn muốn hiển thị kết quả của công thức. (Trong ví dụ này, bài viết sẽ sử dụng ô E6).

2. Nhập công thức bên dưới:

=SUM(COUNTIF(A3:A11, "Red"), COUNTIF(B3:B11, "Big"))

3. Công thức này sẽ đếm các ô chứa táo đỏ, sau đó là số ô chứa táo lớn, và cuối cùng, nó sẽ tính tổng hai số này.

4. Nhấn nút Enter.

5. Bây giờ Excel sẽ đếm và hiển thị số lượng táo cỡ lớn hoặc có đỏ.

Trên đây là 2 bảng dữ liệu với cách sử dụng cho hàm COUNTIFS trong Excel. Người dùng phải viết điều kiện đi kèm với vùng dữ liệu điều kiện để hàm COUNTIFS xác định chính xác.

Cách sửa các lỗi thường gặp khi dùng hàm COUNTIFS trong Excel

COUNTIFS không hoạt động khi đếm các giá trị text

Khi đếm các chuỗi text, chúng phải được chèn vào bên trong dấu ngoặc kép. Nếu không, hàm COUNTIFS sẽ không thể đếm chuỗi văn bản và sẽ trả về giá trị 0. Ở ảnh ví dụ sau, chuỗi text chưa được đặt vào trong dấu ngoặc kép. Vì thế, công thức này trả kết quả bằng 0.

Để khắc phục lỗi này, bạn chỉ cần viết lại chính xác công thức: =COUNTIFS(E5:E12, "Car")

COUNTIFS không hoạt động vì sai tham chiếu phạm vi

Khi dùng nhiều hơn một tiêu chí trong hàm COUNTIFS, phạm vi của ô cho tiêu chí khác phải có cùng số lượng ô. Nếu không, hàm COUNTIFS sẽ không hoạt động.

Giả sử ở ví dụ này muốn đếm doanh số bán ô tô ở Austin. Công thức được nhập là =COUNTIFS(E5:E12,"Car",D5:D11,"Austin"). Quan sát kỹ công thức, bạn sẽ thấy phạm vi tiêu chí đầu tiên ở đây là E5:E12 nhưng phạm vi ở tiêu chí thứ hai là D5:D11. Số ô trong phạm vi cho tiêu chí này không giống nhau.

Giờ nhấn Enter, công thức sẽ trả về lỗi #VALUE!.

Viết lại công thức đúng như sau: =COUNTIFS(E5:E12,"Car",D5:D12,"Austin")

COUNTIFS không hoạt động vì lỗi trong công thức

Nếu không chèn công thức chính xác, hàm COUNTIFS sẽ không hoạt động. Khi dùng toán tử toán học bất kỳ, chẳng hạn như dấu lớn hơn (>), nhỏ hơn (<), bằng (=) và không bằng (<>), cả toán tử và tiêu chí số phải được nhập vào bên trong cùng một phương trình. Ví dụ, nếu muốn tìm doanh số bán hàng lớn hơn 100.000USD, bạn cần chèn công thức sau:

=COUNTIFS(F5:F12,">" 100000)

Ở đây, mới chỉ chèn toán tử bên trong phương trình, không có tiêu chí số.

Nếu nhấn Enter, hộp thoại nhắn tin của Microsoft Excel sẽ hiện: “There’s a problem with this formula”.

Để khắc phục vấn đề. Gõ công thức chính xác:

=COUNTIFS(F5:F12,">100000")

Giờ chúng ta đã nhập cả hai toán tử và tiêu chí bên trong dấu ngoặc. Giờ công thức này sẽ trả về số lượng theo yêu cầu.

Nhấn Enter.

Kết quả, bạn sẽ nhận được số lượng bán hàng lớn hơn 100.000USD.

Chúc các bạn thực hiện thành công!