Hàm SUMIFS, COUNTIFS: Bậc Thầy Thống Kê Có Điều Kiện Trong Excel (Kèm Ví Dụ)

Trong thế giới phân tích dữ liệu với Excel, việc tính tổng hay đếm các giá trị đơn giản bằng hàm SUM và COUNT là chưa đủ khi bạn cần xử lý các bộ dữ liệu phức tạp. Đây là lúc hàm SUMIFS COUNTIFS tỏa sáng, trở thành công cụ không thể thiếu để thực hiện thống kê có điều kiện dựa trên nhiều tiêu chí cùng lúc. Bài viết này sẽ cung cấp hướng dẫn chi tiết từ A-Z về cách sử dụng hiệu quả hai hàm mạnh mẽ này, giúp bạn nâng cao kỹ năng Excel và xử lý dữ liệu chuyên nghiệp hơn.

Việc thành thạo hàm SUMIFS COUNTIFS không chỉ giúp bạn tiết kiệm thời gian mà còn đảm bảo tính chính xác khi tổng hợp thông tin từ các bảng tính lớn. Hãy cùng khám phá cách làm chủ chúng!

Hàm SUMIFS là gì và tại sao bạn cần nó?

Hàm SUMIFS trong Excel được sử dụng để tính tổng các giá trị trong một phạm vi (sum_range) đáp ứng nhiều điều kiện hoặc tiêu chí khác nhau. Đây là phiên bản nâng cấp của hàm SUMIF, vốn chỉ cho phép áp dụng một điều kiện duy nhất.

Khi bạn cần phân tích dữ liệu bán hàng theo sản phẩm VÀ khu vực, hoặc tính tổng lương cho nhân viên thuộc phòng ban X VÀ có chức danh Y, SUMIFS chính là giải pháp tối ưu.

Cú pháp Hàm SUMIFS

Cú pháp chuẩn của hàm SUMIFS như sau:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• sum_range (bắt buộc): Phạm vi các ô cần tính tổng.
• criteria_range1 (bắt buộc): Phạm vi ô đầu tiên sẽ được đánh giá dựa trên criteria1.
• criteria1 (bắt buộc): Điều kiện áp dụng cho criteria_range1. Điều kiện này có thể là số, biểu thức, tham chiếu ô, hoặc văn bản.
• criteria_range2, criteria2, ... (tùy chọn): Các cặp phạm vi và điều kiện bổ sung. Bạn có thể thêm tối đa 127 cặp phạm vi/điều kiện.

Lưu ý quan trọng: Kích thước và hình dạng của sum_range và tất cả các criteria_range phải giống nhau. Nếu không, công thức có thể trả về lỗi #VALUE!.

Ví dụ thực tế về Hàm SUMIFS

Giả sử chúng ta có bảng dữ liệu bán hàng sau:

[Gợi ý: Chèn ảnh minh họa bảng dữ liệu Excel gồm các cột: Ngày, Sản Phẩm, Khu Vực, Số Lượng, Doanh Thu tại đây]

Yêu cầu: Tính tổng Doanh Thu của “Sản phẩm A” bán được tại “Khu Vực Bắc”.

Công thức SUMIFS sẽ là:

=SUMIFS(E2:E10, B2:B10, "Sản phẩm A", C2:C10, "Khu Vực Bắc")

• E2:E10: Phạm vi chứa Doanh Thu cần tính tổng.
• B2:B10: Phạm vi chứa tên Sản Phẩm.
• "Sản phẩm A": Điều kiện cho cột Sản Phẩm.
• C2:C10: Phạm vi chứa tên Khu Vực.
• "Khu Vực Bắc": Điều kiện cho cột Khu Vực.

Excel sẽ duyệt qua từng hàng, kiểm tra xem Sản Phẩm có phải là “Sản phẩm A” VÀ Khu Vực có phải là “Khu Vực Bắc” không. Nếu cả hai điều kiện đều đúng, giá trị Doanh Thu tương ứng ở cột E sẽ được cộng vào tổng.

Hàm COUNTIFS: Đếm chính xác với nhiều tiêu chí

Tương tự như SUMIFS, hàm COUNTIFS cho phép bạn đếm số lượng ô trong một hoặc nhiều phạm vi đáp ứng đồng thời nhiều điều kiện. Đây là bản mở rộng của hàm COUNTIF (chỉ xử lý một điều kiện).

COUNTIFS cực kỳ hữu ích khi bạn muốn biết có bao nhiêu đơn hàng đã hoàn thành TRONG tháng này, hoặc đếm số lượng nhân viên có kinh nghiệm trên 5 năm VÀ thuộc bộ phận kỹ thuật.

Cú pháp Hàm COUNTIFS

Cú pháp của hàm COUNTIFS như sau:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• criteria_range1 (bắt buộc): Phạm vi ô đầu tiên cần đánh giá theo criteria1.
• criteria1 (bắt buộc): Điều kiện áp dụng cho criteria_range1.
• criteria_range2, criteria2, ... (tùy chọn): Các cặp phạm vi và điều kiện bổ sung. Tối đa 127 cặp.

Lưu ý: Tất cả các criteria_range phải có cùng số hàng và số cột. Hàm COUNTIFS chỉ đếm các hàng/ô mà TẤT CẢ các điều kiện tương ứng đều được thỏa mãn.

Ví dụ thực tế về Hàm COUNTIFS

Sử dụng lại bảng dữ liệu bán hàng ở trên.

Yêu cầu: Đếm số lần “Sản phẩm B” được bán với “Số Lượng” lớn hơn 50.

Công thức COUNTIFS sẽ là:

=COUNTIFS(B2:B10, "Sản phẩm B", D2:D10, ">50")

• B2:B10: Phạm vi chứa tên Sản Phẩm.
• "Sản phẩm B": Điều kiện 1 (phải là Sản phẩm B).
• D2:D10: Phạm vi chứa Số Lượng.
• ">50": Điều kiện 2 (Số Lượng phải lớn hơn 50). Lưu ý toán tử so sánh phải đặt trong dấu ngoặc kép.

Excel sẽ đếm số hàng mà cột B là “Sản phẩm B” VÀ cột D có giá trị lớn hơn 50.

[Gợi ý: Chèn ảnh minh họa công thức COUNTIFS và kết quả đếm được tại đây]

Mẹo và Thủ thuật Nâng cao với Hàm SUMIFS COUNTIFS

Sử dụng Ký tự đại diện (Wildcards)

Bạn có thể dùng ký tự đại diện để tạo điều kiện linh hoạt hơn:

• Dấu sao (*): Đại diện cho bất kỳ chuỗi ký tự nào. Ví dụ: "Sản phẩm*" sẽ khớp với “Sản phẩm A”, “Sản phẩm B”, v.v.
• Dấu hỏi (?): Đại diện cho một ký tự đơn lẻ bất kỳ. Ví dụ: "B?n" sẽ khớp với “Bàn”, “Bản”.

Ví dụ: Đếm tất cả sản phẩm bắt đầu bằng “Sản phẩm” tại Khu Vực Nam:

=COUNTIFS(B2:B10, "Sản phẩm*", C2:C10, "Khu Vực Nam")

Làm việc với Ngày tháng và Toán tử so sánh

Khi sử dụng điều kiện là ngày tháng hoặc các toán tử so sánh (>, <, >=, <=, <>), hãy đặt chúng trong dấu ngoặc kép:

• Tính tổng doanh thu sau ngày 01/01/2024: SUMIFS(E2:E10, A2:A10, ">"&DATE(2024,1,1)) (Nên dùng hàm DATE hoặc tham chiếu ô chứa ngày để đảm bảo đúng định dạng).
• Đếm số lượng khác 100: COUNTIFS(D2:D10, "<>100")

Tham chiếu đến ô chứa điều kiện

Thay vì nhập trực tiếp điều kiện vào công thức, bạn có thể tham chiếu đến một ô chứa điều kiện đó. Điều này giúp công thức linh hoạt và dễ cập nhật hơn.

Ví dụ, nếu ô G1 chứa “Sản phẩm A” và G2 chứa “Khu Vực Bắc”:

=SUMIFS(E2:E10, B2:B10, G1, C2:C10, G2)

Khi bạn thay đổi giá trị trong G1 hoặc G2, kết quả sẽ tự động cập nhật.

Các lỗi thường gặp và cách khắc phục

• #VALUE!: Thường xảy ra khi các phạm vi (sum_range và criteria_range) không có kích thước giống nhau trong SUMIFS, hoặc các criteria_range không giống nhau trong COUNTIFS.
• Kết quả bằng 0 không mong muốn: Kiểm tra lại điều kiện (chính tả, khoảng trắng thừa), định dạng dữ liệu (số lưu dưới dạng văn bản), và logic công thức.

Kết luận

Hàm SUMIFS COUNTIFS là những công cụ cực kỳ mạnh mẽ trong Excel, cho phép bạn thực hiện các phép tính tổng và đếm phức tạp dựa trên nhiều điều kiện một cách dễ dàng và chính xác. Việc nắm vững cú pháp, cách sử dụng các toán tử, ký tự đại diện và tham chiếu ô sẽ giúp bạn khai thác tối đa tiềm năng của chúng.

Hãy bắt đầu áp dụng ngay những kiến thức này vào công việc phân tích dữ liệu hàng ngày của bạn. Đừng quên thực hành với các bộ dữ liệu khác nhau để trở nên thành thạo hơn. Nếu bạn muốn tìm hiểu thêm về các hàm Excel cơ bản khác, hãy xem qua bài viết tổng hợp các hàm Excel cơ bản của chúng tôi.

Để tham khảo thêm thông tin chính thức từ Microsoft, bạn có thể truy cập tài liệu về hàm SUMIFS và hàm COUNTIFS.

Bạn có mẹo hay nào khác khi sử dụng hàm SUMIFS và COUNTIFS không? Hãy chia sẻ trong phần bình luận bên dưới nhé!