Tổng hợp

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

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.

    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 đó:

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

    • 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ự đó.

    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.

    Tính số lượng Nam 25 ngày công

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

    Kết quả số lượng Nam 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.

    Tính số lượng Nam nghỉ 0 ngày

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

    Số lượng Nam nghỉ 0 ngày

    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,” rồi nhấn Enter.

    Số lượng Nam 25 ngày, ngày nghỉ ít hoặc bằng 2

    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.

    Tổng số lượng Nam 25 ngày, ngày nghỉ ít hoặc bằng 2

    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,” rồi nhấn Enter.

    Sản phẩm Đài Loan giá dưới 200,000đ

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

    Tổng sản phẩm Đài Loan giá dưới 200,000đ

    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,” rồi nhấn Enter.

    Sản phẩm Đài Loan trong khoảng 100,000đ đến 150,000đ

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

    Tổng sản phẩm Đài Loan trong khoảng 100,000đ đến 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.

    Ví dụ hàm COUNITFS trong Excel
    Ví dụ hàm COUNITFS trong Excel

    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.

    Ví dụ kết quả của hàm Excel COUNTIFS
    Ví dụ kết quả của hàm Excel COUNTIFS

    Hãy quan sát cách công thức đếm các ô có cả thuộc tính redbig. 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ó đỏ.

    Kết hợp hàm COUNTIF với hàm SUM
    Kết hợp hàm COUNTIF với hàm SUM

    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.

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

    Đăng bởi: Trường Cao Đẳng Sư phạm Hà Nội

    Chuyên mục: Tổng hợp

    Trả lời

    Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

    Back to top button

    Bạn đang dùng trình chặn quảng cáo!

    Bạn đang dùng trình chặn quảng cáo!