Hàm SUM quá cơ bản, hãy sử dụng SUMIFS thay thế!

Bạn đã sử dụng hàm SUM trong nhiều năm. Nó cộng số, thực hiện đúng chức năng, và có lẽ bạn chẳng cần suy nghĩ nhiều về nó. Nhưng trong khi bạn loay hoay với các bộ lọc và xây dựng những cột hỗ trợ lộn xộn, Excel vẫn luôn chờ đợi bạn.

Có một hàm thông minh hơn, ẩn mình ngay trước mắt, có thể giúp bạn làm những việc nặng nhọc.

Tại sao chỉ dùng hàm SUM thôi là chưa đủ?

SUM trong bảng tính tương đương với một lời mời mở: Nó bao gồm mọi thứ vào tổng, bất kể chúng có thuộc về tổng hay không. Điều này hoàn hảo khi bạn chỉ cần tính tổng nhanh cho các cột, nhưng trong công việc thực tế, bạn hiếm khi phải cộng tất cả mọi thứ.

Hãy tưởng tượng bạn là một quản trị viên tại trường học đang nhìn chằm chằm vào một bảng tính Excel khổng lồ chứa hàng nghìn hồ sơ học sinh. Bạn muốn biết tổng điểm của một học sinh, chẳng hạn như John, trong mỗi bài kiểm tra. Chỉ với hàm SUM, điều đó có nghĩa là bạn phải tự tay tìm từng hàng có John xuất hiện, cộng điểm của cậu ấy từng cái một, và hy vọng không bỏ sót ô nào.

=SUM(C2,C4,C6,...)

Công thức này rất tẻ nhạt, dễ xảy ra lỗi, và càng tệ hơn khi tập dữ liệu của bạn lớn hơn.

SUM không phân biệt cột là tên, phòng ban hay dòng sản phẩm. Nó chỉ đơn giản cộng bất kỳ số nào bạn đưa vào. Điều này ổn với các danh sách đơn giản, nhưng khi bạn muốn phân biệt kết quả của John với kết quả của Sarah, hoặc lọc ra tất cả trừ một phòng ban, SUM lại không đáp ứng được.

Ngược lại, SUMIFS được thiết kế cho mục đích này. Thay vì cộng tất cả dữ liệu trong tầm mắt, nó sẽ hỏi bạn những số nào thuộc về tổng. Công thức SUMIFS trông như sau:

=SUMIFS(C2:C10,A2:A10,"John")

Nói một cách dễ hiểu, công thức này yêu cầu Excel xem xét các tên trong cột A, tìm tất cả những hàng có chữ "John", sau đó cộng các điểm trùng khớp từ cột C. Bạn sẽ không cần lọc, quét thủ công hay các cột hỗ trợ - chỉ cần một công thức.

Và nó không chỉ dành cho dữ liệu sinh viên. Hãy tưởng tượng việc theo dõi chi phí dự án theo phòng ban, phân tích doanh số theo khu vực, hoặc tính toán hoa hồng theo nhân viên bán hàng và khu vực. SUM sẽ buộc bạn phải tìm cách giải quyết rắc rối, nhưng SUMIFS sẽ cho bạn tổng số dựa trên các điều kiện bạn chỉ định.

Giờ hãy nghĩ đến việc khi nào tiêu chí của bạn thay đổi, điều mà chúng luôn xảy ra. Có thể bạn không cần tổng số của quý này nữa; mà là tổng số của tháng trước. Có thể bạn muốn loại trừ một dòng sản phẩm nhất định, hoặc chỉ bao gồm các đơn hàng vượt quá một giá trị nhất định. Với SUM, bạn sẽ phải lọc và chọn lại các phạm vi một lần nữa. Với SUMIFS, bạn chỉ cần thay đổi điều kiện và công thức sẽ tự động điều chỉnh.

SUMIFS thông minh hơn cho công việc thực tế

Cú pháp của hàm SUMIFS được thiết kế để xử lý nhiều điều kiện cùng một lúc và chỉ tính tổng các giá trị đáp ứng tất cả chúng:

=SUMIFS (sum_range, criteria_range1, criteria1, criteria_range 2, criteria2, [criteria_range3, criteria3], …)

Thoạt nhìn, nó có vẻ hơi phức tạp. Nhưng khi bạn phân tích kỹ hơn, nó thực sự rất đơn giản: Bạn yêu cầu Excel cộng các số trong sum_range đã chỉ định, nhưng chỉ khi criteria1 đúng trong criteria_range1, và criteria2 đúng trong criteria_range2, v.v...

Lưu ý: Ngoài ra còn có SUMIF, hoạt động theo cùng một cách nhưng chỉ cho phép một điều kiện duy nhất. Mọi người thường dùng SUMIFS, ngay cả với một điều kiện, vì nếu sau này cần thêm một điều kiện nữa, họ không cần phải viết lại công thức.

Dưới đây là một ví dụ thực tế về hàm SUMIFS với dữ liệu bán hàng:

=SUMIFS (Revenue, Region, "East", Sales Channel, "Online", Price, ">500")
=SUMIFS(L2:L100,A2:A100,"Asia",D2:D100,"Online",J2:J100,">400")

Về cơ bản, công thức này yêu cầu Excel duyệt qua cột doanh thu (trong L2:L100) và cộng các giá trị, nhưng chỉ khi khu vực (trong A2:A100) là Asia, kênh bán hàng (trong D2:D100) là trực tuyến và giá (trong J2:J100) lớn hơn $500.

Bạn có thể tiếp tục cộng dồn thêm các điều kiện khác - quốc gia, ngày, nhân viên bán hàng, mức độ ưu tiên đơn hàng, dòng sản phẩm - và Excel sẽ kiểm tra từng điều kiện. Chỉ những hàng đáp ứng mọi điều kiện mới được tính vào tổng. SUMIFS có thể xử lý tới 127 tiêu chí, quá đủ cho cả những báo cáo phức tạp nhất.

Điều tuyệt vời nhất là công thức không phải là công thức tĩnh. Nếu dữ liệu của bạn thay đổi, chẳng hạn như một hàng đáp ứng các điều kiện, công thức sẽ tự động cập nhật. Điều này rất hữu ích cho việc theo dõi doanh số theo khu vực, tóm tắt chi phí theo danh mục hoặc theo dõi hàng tồn kho theo loại sản phẩm.

Và nếu bạn còn thắc mắc, ngày tháng không phải là vấn đề đối với SUMIFS. Bạn có thể sử dụng công thức này để tính tổng sau (hoặc trước) một số ngày cụ thể bằng một công thức đơn giản, như sau:

=SUMIFS (Revenue, Order Date, ">=1/1/2016")
=SUMIFS(N2:N100,F2:F100,">=1/1/2016")

Chi tiết duy nhất cần lưu ý là khi tiêu chí của bạn không phải là số - chẳng hạn như ngày tháng hoặc văn bản - bạn cần đặt nó trong dấu ngoặc kép.

SUMIFS hoạt động trên tất cả các phiên bản Excel hiện đại, bắt đầu từ Excel 2007, và hoạt động ổn định dù bạn đang sử dụng ứng dụng desktop, Excel Online hay thậm chí là ứng dụng di động. Công thức này cũng được hỗ trợ đầy đủ trong Google Sheets, nếu bạn thích nó hơn Excel, cả trên web và trong ứng dụng di động.

Dù bạn làm việc ở đâu, SUMIFS vẫn là một bản nâng cấp đáng tin cậy so với công thức SUM truyền thống.