Hàm SUMIFS() trong Excel: Cộng dữ liệu với nhiều điều kiện

Hàm SUMIFS() trong Excel cộng các số dựa trên nhiều điều kiện, giúp bạn kiểm soát phép tính một cách chính xác. Khác với SUMIF() chỉ kiểm tra một điều kiện, SUMIFS() xử lý đồng thời nhiều điều kiện.

Trong bài viết này, tôi sẽ hướng dẫn bạn cách dùng SUMIFS() với các ví dụ nâng cao và mẹo xử lý lỗi. Kết thúc bài, bạn sẽ biết cách dùng SUMIFS() để tính toán chính xác và hiệu quả hơn.

Câu trả lời nhanh

Để dùng hàm SUMIFS() trong Excel:

• Gõ =SUMIFS( để bắt đầu hàm.

• Chọn vùng chứa các giá trị cần cộng.

• Chọn vùng nơi áp dụng điều kiện.

• Nhập điều kiện cần khớp.

• Đóng ngoặc và nhấn Enter.

Ở đây, tôi đã cộng tất cả doanh số lớn hơn 200.

=SUMIFS(B2:B8, B2:B8, ">200")

Cộng các giá trị bằng SUMIFS(). Ảnh: Tác giả.

Tìm hiểu hàm SUMIFS()

Hàm SUMIFS() trong Excel chỉ cộng các giá trị khi tất cả điều kiện chỉ định đều thỏa. Hàm hỗ trợ các toán tử logic như lớn hơn >, nhỏ hơn <, bằng =, và khác <>, cũng như ký tự đại diện để khớp một phần. Hàm hoạt động với số, văn bản và ngày tháng.

Cú pháp của SUMIFS()

Hàm SUMIFS() trước hết xác định vùng giá trị cần cộng, theo sau là các cặp vùng điều kiện và tiêu chí. Lưu ý SUMIFS() có thể xử lý nhiều điều kiện logic.

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

Trong đó:

• sum_range là vùng ô bạn muốn cộng.

• criteria_range1 là vùng đầu tiên để kiểm tra điều kiện.

• criteria1 là tiêu chí áp dụng cho criteria_range1.

• [criteria_range2, criteria2], … là tùy chọn. Bạn có thể thêm nhiều cặp vùng và điều kiện. Mỗi cặp thêm một điều kiện bắt buộc phải thỏa.

Lưu ý criteria_range (vùng kiểm tra điều kiện) phải có cùng số hàng và cột với sum_range (vùng được cộng).

Ví dụ về SUMIFS()

Hãy thử một ví dụ cơ bản. Tôi có bộ dữ liệu về các loại trái cây với số lượng và doanh số. Tôi muốn tìm tổng doanh số của Táo khi Số lượng lớn hơn 5. Khi đó công thức của tôi sẽ là:

=SUMIFS(C2:C8, A2:A8, "Apples", B2:B8, ">5")

Ở đây C2:C8 là vùng giá trị cần cộng (Sales ($)). A2:A8, "Apples" là điều kiện chỉ lấy các hàng có sản phẩm là Táo. Và B2:B8, ">5" là điều kiện chỉ lấy các hàng có Số lượng lớn hơn 5.

Công thức SUMIFS() để cộng doanh số theo điều kiện. Ảnh: Tác giả.

SUMIF() so với SUMIFS()

Khác biệt chính giữa SUMIF() và SUMIFS() là số lượng điều kiện. SUMIF() xử lý một điều kiện duy nhất, còn SUMIFS() có thể xử lý nhiều điều kiện.

Các trường hợp sử dụng SUMIFS() trong Excel

Giờ bạn đã biết cách hoạt động của SUMIFS(), hãy xem vài tình huống nâng cao có thể áp dụng trong thực tế.

Dùng SUMIFS() với các kiểu dữ liệu khác nhau

SUMIFS() có thể làm việc với nhiều kiểu dữ liệu như số, văn bản và ngày tháng. Giả sử tôi có báo cáo bán hàng, nhưng một số hàng thiếu tên khách hàng. Tôi muốn chỉ cộng những hàng có tên khách. Khi đó, tôi dùng công thức sau:

=SUMIFS(C2:C5, A2:A5, "<>")

Công thức này xem cột Customer, kiểm tra các ô không trống, rồi cộng doanh số tương ứng.

Cộng tất cả ô không trống bằng hàm SUMIFS(). Ảnh: Tác giả.

Để cộng doanh số ở những hàng thiếu tên khách, tôi dùng công thức này:  

=SUMIFS(C2:C10, A2:A10, "", B2:B10, "")

Cộng các ô trống bằng hàm SUMIFS(). Ảnh: Tác giả.

Thay vì mã hóa cứng điều kiện, ta có thể lưu trong một ô khác. Nếu tôi muốn cộng doanh số cho Sarah Lee, tôi gõ tên vào ô E3 và dùng công thức:

=SUMIFS(C2:C5, A2:A5, E3)

Mỗi khi giá trị trong E3 thay đổi, tổng sẽ tự động cập nhật.

Dùng SUMIFS() để cộng theo tham chiếu ô. Ảnh: Tác giả.

Áp dụng logic OR trong SUMIFS()

Ta biết SUMIFS() chỉ dùng logic AND, nghĩa là mọi điều kiện đặt ra đều phải đúng thì mới cộng. Tuy nhiên, nếu muốn dùng logic OR (cộng khi bất kỳ một trong nhiều điều kiện thỏa), ta có thể kết hợp nhiều SUMIFS().

Ví dụ, nếu tôi muốn cộng doanh số của John Doe HOẶC Drake White, tôi kết hợp hai công thức SUMIFS() như sau:

=SUMIFS(C2:C8, A2:A8, "John Doe") + SUMIFS(C2:C8, A2:A8, "Drake White")

Vì Drake White không có trong danh sách nên phần đó trả về 0. Tổng chỉ gồm doanh số của John Doe, minh họa cách hoạt động của logic OR.

Dùng logic OR trong hàm SUMIFS(). Ảnh: Tác giả.

Trong Excel 365 hoặc mới hơn, ta có thể đơn giản hóa bằng công thức mảng và vẫn cho kết quả tương tự.

=SUMIFS(C2:C8,A2:A8,{"John Doe","Drake White"})

Dùng công thức mảng SUMIFS(). Ảnh: Tác giả.

SUMIFS() với vùng được đặt tên

Ta cũng có thể dùng vùng được đặt tên thay cho tham chiếu ô như A2:A5 hoặc C2:C5. Trong ví dụ này, tôi chọn A2:A5 và đặt tên Customer_list, chọn C2:C5 và đặt tên Sales_list. Giờ để tìm tổng doanh số của John, tôi dùng công thức: 

=SUMIFS(Sales_list, Customer_list, "John")

Cộng giá trị bằng SUMIFS() với vùng đặt tên. Ảnh: Tác giả.

Dùng ký tự đại diện để khớp một phần

Ký tự đại diện là các ký tự đặc biệt (* hoặc ?) đại diện cho các ký tự không xác định hoặc biến thiên trong tìm kiếm văn bản và khớp mẫu: 

• * khớp với bất kỳ số lượng ký tự nào (kể cả 0).

• ? khớp chính xác một ký tự.

Cách hoạt động như sau: 

• A* sẽ khớp mọi ô bắt đầu bằng A.

• *A sẽ khớp mọi ô kết thúc bằng A.

• *A* sẽ khớp mọi ô có chứa A ở bất kỳ đâu.

Hàm SUMIFS() hỗ trợ khớp văn bản thông qua ký tự đại diện. Nó xác định các giá trị bắt đầu bằng, kết thúc bằng hoặc chứa các mẫu văn bản cụ thể. Giả sử tôi muốn cộng doanh số cho Mã sản phẩm bắt đầu bằng ABC. Khi đó, công thức sẽ như sau:

=SUMIFS(D2:D8, C2:C8, "ABC*")

Để cộng doanh số kết thúc bằng 00, tôi viết:

=SUMIFS(B2:B7, A2:A7, "*00")

Dùng ký tự đại diện trong SUMIFS(). Ảnh: Tác giả.

Cộng theo khoảng ngày

SUMIFS() cũng có thể cộng các giá trị trong một khoảng ngày cụ thể. Nếu tôi muốn biết tất cả doanh số trong tháng 1 năm 2023, tôi dùng công thức sau: 

=SUMIFS(D2:D8, B2:B8, ">=01-Jan-2023", B2:B8, "<=31-Jan-2023") 

Công thức này sẽ kiểm tra các ngày từ 01/01/2023 đến 31/01/2023 rồi cộng tất cả doanh số.

Cộng theo khoảng ngày bằng SUMIFS(). Ảnh: Tác giả.

Cộng giá trị với nhiều điều kiện số

Hàm SUMIFS() có thể cộng các giá trị nằm trong những khoảng số cụ thể. Ở đây, tôi có một bộ dữ liệu ngẫu nhiên và muốn cộng các giá trị có Doanh số lớn hơn 100 nhưng nhỏ hơn 300. Tôi dùng công thức sau:  

=SUMIFS(C2:C5, C2:C5, ">100", C2:C5, "<300")

Công thức này trước hết chỉ định vùng ô cần cộng (C2:C5) làm đối số đầu tiên. Sau đó áp dụng hai điều kiện như trên cho cùng một vùng. Hàm SUMIFS() đồng thời đánh giá mỗi ô trong vùng theo cả hai tiêu chí. Bạn sẽ thấy chỉ những ô có giá trị nằm giữa 100 và 300 (không gồm biên) mới được cộng.

Cộng các giá trị trong một khoảng số bằng SUMIFS(). Ảnh: Tác giả.

Khắc phục sự cố khi SUMIFS() không hoạt động

Nếu hàm SUMIFS() không cho kết quả như mong đợi, hãy kiểm tra các vấn đề sau:

Kích thước vùng không khớp

Nếu công thức SUMIFS() trả về lỗi, có thể do kích thước vùng không khớp. Trong SUMIFS(), tất cả các vùng bạn chọn như sum_range và criteria_ranges phải có cùng số hàng và cột.

Ví dụ, trong công thức này vùng cần cộng là B2:B8, nhưng vùng tiêu chí là A2:A7, thiếu một hàng:

=SUMIFS(B2:B8, A2:A7, "Drake White")

Sự không khớp này sẽ gây lỗi #VALUE!. Vì vậy, hãy đảm bảo mọi vùng có cùng kích thước, như sau:

=SUMIFS(B2:B8, A2:A8, "John Doe")

Định dạng tiêu chí văn bản không đúng

Trong SUMIFS(), bạn phải đặt tiêu chí văn bản trong dấu ngoặc kép. Nếu không, có thể trả về kết quả không đúng. Ví dụ, công thức này sẽ trả về 0 vì Excel xem Drake White là tên hoặc biến, không phải văn bản: 

=SUMIFS(B2:B8, A2:A8, Drake White)

Để khắc phục, đặt Drake White trong dấu ngoặc kép như thế này để có kết quả đúng:

=SUMIFS(B2:B8, A2:A8, "Drake White")

Giá trị trả về 0 ngoài ý muốn

Nếu công thức trả về 0 khi bạn kỳ vọng có kết quả, có thể do định dạng điều kiện số hoặc ngày không đúng. Trong SUMIFS(), các điều kiện như >100 hoặc <01-Jan-2023 phải được viết như chuỗi văn bản trong ngoặc kép. Nếu không, sẽ trả về lỗi hoặc 0 vì điều kiện không nằm trong dấu ngoặc.

Hoặc ta có thể lưu ngày trong một ô riêng (ví dụ C1) và dùng:

=SUMIFS(B2:B8, A2:A8, ">="&C1)

SUMIFS() không hoạt động với biến đổi mảng

SUMIFS() không hỗ trợ một số biến đổi như trích xuất thành phần cụ thể từ dữ liệu ngay trong công thức. Ví dụ, nếu tôi muốn cộng doanh số của tháng Một và tháng Ba, SUMIFS() sẽ không hoạt động trực tiếp với tiêu chí mảng {"January","March"}. 

Để minh họa, giả sử tôi có các tháng ở cột A (January, February, March, April) và giá trị doanh số ở cột B (100, 200, 300, 400). Công thức

=SUMIFS(B2:B5, A2:A5, {"January","March"})

sẽ không trả về tổng kỳ vọng là 400 (100 + 300). Thay vào đó, ta có thể dùng SUMPRODUCT():

=SUMPRODUCT((A2:A5={"January","March"})*B2:B5)

hoặc bọc SUMIFS() bằng SUM() với công thức này:

=SUM(SUMIFS(B2:B5, A2:A5, {"January","March"}))

Cả hai cách đều xử lý đúng tiêu chí mảng và trả về tổng doanh số cho tháng Một và tháng Ba.

Thực hành tốt khi dùng SUMIFS()

Để tận dụng tối đa hàm SUMIFS(), chúng ta cần tránh lỗi thường gặp bằng cách làm theo các thực hành sau: 

• Dùng ký tự đại diện để lọc linh hoạt: Nên dùng ký tự đại diện khi lọc dữ liệu vì đôi khi dữ liệu không hoàn hảo. Tên sản phẩm có thể không được viết thống nhất hoặc có khoảng trắng thừa. Ký tự đại diện giúp bạn khớp theo mẫu mà không cần văn bản chính xác tuyệt đối.

• Tránh mã hóa cứng tiêu chí: Thay vì gõ trực tiếp điều kiện vào công thức, hãy lưu vào một ô riêng. Nếu bạn mã hóa cứng như "Drake White" hoặc ">100", bạn sẽ phải sửa công thức mỗi lần muốn thay đổi. Với tham chiếu ô, công thức sẽ tự cập nhật.

• Dùng tham chiếu tuyệt đối khi sao chép công thức: Khi bạn sao chép công thức SUMIFS() sang các ô khác, Excel sẽ tự động điều chỉnh tham chiếu ô. Nếu không muốn vậy, hãy dùng tham chiếu tuyệt đối bằng cách thêm $ để cố định ô.

Lời kết

SUMIFS() là hàm Excel giúp chúng ta phân tích dữ liệu bằng cách lọc và cộng các số đáp ứng tiêu chí cụ thể. Hàm rất hữu ích cho các tác vụ như theo dõi doanh số theo khu vực, tổng hợp chi phí theo danh mục, hoặc kiểm kê theo loại sản phẩm. Khi quen tay với SUMIFS(), bạn sẽ thấy nó tiết kiệm thời gian và giúp phát hiện xu hướng quan trọng trong dữ liệu mà không cần giải pháp vòng vo phức tạp.

Nếu bạn muốn nâng cao kỹ năng này, hãy xem các khóa Data Analysis in Excel, Data Preparation in Excel, và Data Visualization in Excel hoặc lộ trình kỹ năng Excel Fundamentals. Chúng sẽ giúp bạn củng cố kỹ năng hiện có.