Hàm SUMIFS, cách dùng hàm tính tổng nhiều điều kiện trong Excel

Hàm Sumifs trong Excel vô cùng hữu ích khi bạn phải tính tổng nhiều điều kiện. Dưới đây là chi tiết cách sử dụng hàm Sumifs trong Microsoft Excel.

Hàm SUMIFS trong Excel là một trong những hàm Excel cơ bản, hàm tính toán thường được dùng trong Excel. Để tính tổng trong Excel chúng ta sẽ dùng đến hàm SUM, nếu muốn thêm 1 điều kiện nhất định cho hàm tính tổng đó sẽ dùng hàm SUMIF. Trong trường hợp bảng dữ liệu yêu cầu tính tổng kèm theo nhiều điều kiện, thì chúng ta phải sử dụng tới hàm SUMIFS. Bài viết dưới đây sẽ hướng dẫn bạn đọc cách sử dụng hàm SUMIFS trong Excel.

• MS Excel - Bài 5: Các công thức và hàm Excel
• Cách kết hợp hàm Sumif và hàm Vlookup trên Excel
• 3 cách tính tổng trong Excel
• Cách sửa lỗi hàm SUM không cộng được trong Excel

Cú pháp hàm SUMIFS trong Excel

Cú pháp hàm SUMIFS trong Excel có dạng =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…).

Trong đó:

• Sum_range: là các ô cần tính tổng trong bảng dữ liệu, những giá trị trống và giá trị text được bỏ qua, tham số bắt buộc phải có.
• Criteria_range1: phạm vi cần được kiểm tra bằng điều kiện criteria1, là giá trị bắt buộc.
• Criteria1: điều kiện áp dụng cho criteria_range1, có thể là một số, biểu thức, tham chiếu ô để xác định ô nào trong criteria_range1 sẽ được tính tổng, cũng là giá trị bắt buộc.
• Criteria_range2,criteria2,…: tùy chọn các phạm vi và điều kiện bổ sung, tối đa 127 cặp criteria_range, criteria.

Lưu ý khi dùng hàm SUMIFS trong Excel

• Mỗi ô trong vùng, phạm vi sum_range sẽ được cộng tổng khi thỏa mãn tất cả các điều kiện tương ứng đã xác định là đúng cho ô đó.
• Các ô trong sum_range có chứa TRUE được coi là 1, các ô chứa FALSE được coi là 0.
• Mỗi criteria_range phải có cùng kích thước vùng chọn giống với sum_range, criteria_range và sum_range phải có cùng số hàng và cùng số cột.
• Criteria có thể dùng ký tự dấu hỏi chấm (?) thay cho ký tự đơn, dấu sao (*) thay cho một chuỗi. Nếu điều kiện là dấu hỏi chấm hay dấu sao thì phải nhập thêm dấu ~ ở phía trước, giá trị điều kiện là văn bản để trong dấu “.
• Nhiều điều kiện được áp dụng bằng logic AND, tức là criteria1 VÀ criteria2, v.v...
• Mỗi phạm vi bổ sung phải có cùng số lượng hàng và cột như tổng dải ô, nhưng các phạm vi không cần phải liền kề. Nếu bạn cung cấp các phạm vi không khớp, bạn sẽ gặp lỗi #VALUE.
• Chuỗi văn bản trong tiêu chí phải được đặt trong dấu ngoặc kép (""), tức là "apple", ">32", "jap*"
• Tham chiếu ô trong tiêu chí không được đặt trong dấu ngoặc kép, tức là "<"&A1
• SUMIF và SUMIFS có thể xử lý các phạm vi, nhưng không thể xử lý các mảng. Điều này có nghĩa là bạn không thể sử dụng các hàm khác như YEAR trên phạm vi tiêu chí vì kết quả là một mảng. Nếu bạn cần chức năng này, hãy sử dụng hàm SUMPRODUCT.
• Thứ tự của các đối số giữa các hàm SUMIFS và SUMIF là khác nhau. Phạm vi tổng là đối số đầu tiên trong SUMIFS, nhưng là đối số thứ ba trong SUMIF.

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

Sử dụng các ký tự đại diện

Các ký tự đại diện như ‘*’ và ‘?’ có thể được sử dụng trong đối số tiêu chí khi sử dụng hàm SUMIFS. Sử dụng các ký tự đại diện này sẽ giúp bạn tìm kiếm những kết quả trùng khớp tương tự nhưng không chính xác.

Dấu hoa thị (*) - Nó khớp với bất kỳ chuỗi ký tự nào, có thể được sử dụng sau, trước hoặc với những tiêu chí xung quanh để cho phép sử dụng một phần tiêu chí tìm kiếm.

Ví dụ, nếu bạn áp dụng các tiêu chí sau trong hàm SUMIFS:

• N * - Ngụ ý tất cả các ô trong phạm vi bắt đầu bằng N
• * N - Ngụ ý tất cả các ô trong phạm vi kết thúc bằng N
• * N * - Các ô chứa N

Dấu chấm hỏi (?) - Phù hợp với bất kỳ ký tự đơn nào. Giả sử bạn áp dụng N?r làm tiêu chí. "?" ở đây sẽ thay thế cho một ký tự duy nhất. N?r sẽ tương ứng với North, Nor, v.v... Tuy nhiên, nó sẽ không tính đến Name.

Điều gì sẽ xảy ra nếu dữ liệu đã cho chứa dấu hoa thị hoặc dấu chấm hỏi thực sự?

Trong trường hợp này, bạn có thể sử dụng dấu ngã (~). Bạn cần gõ “~” trước dấu chấm hỏi trong trường hợp đó.

Sử dụng các Named Range với hàm SUMIFS

Named Range là tên mô tả của một tập hợp các ô hoặc dải ô trong một trang tính. Bạn có thể sử dụng các Named Range trong khi sử dụng hàm SUMIFS.

Ví dụ về hàm SUMIFS trong Excel

Chúng ta sẽ tính tổng với bảng dữ liệu dưới đây kèm theo một số điều kiện khác nhau.

Ví dụ 1: Tính tổng sản phẩm mà nhân viên Hoài bán ra có đơn giá mặt hàng dưới 400.000đ.

Tại ô nhập kết quả chúng ta điền công thức =SUMIFS(D2:D7,C2:C7,"Hoài",E2:E7,"<400000") rồi nhấn Enter.

Trong đó:

• D2:D7 là vùng cần tính tổng mặt hàng.
• C2:C7 là vùng giá trị điều kiện tên nhân viên.
• “Hoài” là điều kiện tên nhân viên cần tính số mặt hàng chứa trong vùng C2:C7.
• E2:E7 là điều kiện mặt hàng.
• “400.000” là điều kiện chứa mặt hàng trong vùng E2:E7.

Kết quả ra tổng số lượng mặt hàng như hình dưới đây.

Ví dụ 2: Tính tổng số lượng mặt hàng bán ra của nhân viên Nga có số thứ tự < 5.

Tại ô nhập kết quả chúng ta nhập công thức =SUMIFS(D2:D7,C2:C7,"=Nga",A2:A7,"<5") rồi nhấn Enter. Tại tên nhân viên "Nga" nhập dấu = hoặc bỏ dấu = đều được.

Kết quả chúng ta được tổng các mặt hàng do nhân viên Nga bán có số thứ tự < 5 là 550 mặt hàng.

Ví dụ 3: Tính tổng số lượng sản phẩm đã bán ra của nhân viên Nga, trừ sản phẩm khăn quàng cổ.

Tại ô nhập kết quả, chúng ta nhập công thức =SUMIFS(D2:D7,C2:C7,“Nga”,B2:B7,“<>khăn quàng cổ”). Trong đó dấu <> dùng để loại trừ một đối tượng nào đó trong vùng dữ liệu làm điều kiện.

Kết quả cho ra tổng số sản phẩm như hình dưới đây.

Ví dụ 4:

Tính tổng tất cả chỉ số cho Pokemon thế hệ 1 hệ Thủy:

Điều kiện ở đây là Water và Generation là 1.

Lưu ý: Bộ dữ liệu đầy đủ tiếp tục sau hàng 14, tất cả xuống dưới hàng 759.

Dưới đây là chi tiết từng bước:

1. Chọn ô H3.
2. Gõ =SUMIFS.
3. Click đúp vào lệnh SUMIFS.
4. Chọn phạm vi tính tổng số là C2:C759.
5. Gõ ,
6. Chọn phạm vi cho điều kiện đầu tiên B2:B759 (giá trị loại 1)
7. Chọn tiêu chí (ô F3, có giá trị Water)
8. Gõ ,
9. Chọn phạm vi cho điều kiện thứ hai từ D2:D759 (giá trị Generation)
10. Gõ ,
11. Xác định tiêu chí (ô G3 có giá trị 1).
12. Nhấn Enter.

Lưu ý: Bạn có thể thêm nhiều điều kiện hơn bằng cách lặp lại các bước từ 9 tới 12 trước khi nhấn Enter.

Hàm này giờ tính tổng số của các chỉ số tổng cho Pokemon nước thế hệ thứ nhất. Hàm này có thể được lặp lại cho các thế hệ sau để so sánh chúng.

Trên đây là một số ví dụ về cách sử dụng hàm SUMIFS, tính tổng giá trị có nhiều điều kiện kết hợp. Chúng ta phải sắp xếp các vùng và điều kiện đi kèm hợp lý thì Excel mới nhận dạng được công thức hàm để tính.

Hạn chế của hàm SUMIFS

Hàm SUMIFS có một số hạn chế mà bạn nên biết:

• Các điều kiện trong SUMIFS được kết hợp bởi logic AND. Nói cách khác, tất cả các điều kiện phải là TRUE để một ô được tính vào tổng. Để tính tổng các ô bằng logic OR, bạn có thể sử dụng một giải pháp thay thế trong các trường hợp đơn giản.
• Hàm SUMIFS yêu cầu phạm vi thực tế cho tất cả các đối số phạm vi; bạn không thể sử dụng một mảng. Điều này có nghĩa là bạn không thể thực hiện những việc như trích xuất năm từ ngày bên trong hàm SUMIFS. Để thay đổi các giá trị xuất hiện trong đối số phạm vi trước khi áp dụng tiêu chí, hàm SUMPRODUCT là một giải pháp linh hoạt.
• SUMIFS không phân biệt chữ hoa chữ thường. Để tính tổng các giá trị dựa trên điều kiện phân biệt chữ hoa chữ thường, bạn có thể sử dụng công thức dựa trên hàm SUMPRODUCT với hàm EXACT.

Cách phổ biến nhất để giải quyết các giới hạn trên là sử dụng hàm SUMPRODUCT. Trong phiên bản Excel hiện tại, một tùy chọn khác là sử dụng các hàm BYROW và BYCOL mới hơn.

Cách sửa lỗi #VALUE trong hàm SUMIFS của Excel

Vấn đề: Công thức tham chiếu tới ô trong workbook đã đóng

Hàm SUMIF tham chiếu tới một ô hoặc phạm vi ô trong workbook đã đóng sẽ cho kết quả ở dạng báo lỗi #VALUE!.

Lưu ý: Đây là một lỗi phổ biến. Bạn cũng hay bắt gặp thông báo này khi dùng các hàm Excel khác như COUNTIF, COUNTIFS, COUNTBLANK…

Giải pháp:

Mở workbook nằm trong công thức này, nhấn F9 để làm mới lại công thức. Bạn cũng có thể khắc phục vấn đề bằng cách dùng cả hai hàm SUM và IF trong công thức mảng.

Vấn đề: Chuỗi tiêu chí dài hơn 255 kí tự

Hàm SUMIFS trả về kết quả sai khi bạn cố gắng kết hợp các chuỗi dài hơn 255 ký tự.

Giải pháp:

Rút ngắn chuỗi nếu có thể. Nếu không thể làm việc đó, dùng hàm CONCATENATE hoặc toán tử & để chia giá trị thành nhiều chuỗi. Ví dụ:

=SUMIF(B2:B12,"long string"&"another long string")

Vấn đề: Trong SUMIFS, đối số criteria_range không nhất quán với đối số sum_range.

Đối số range luôn phải giống nhau trong SUMIFS. Điều đó có nghĩa các đối số criteria_range và sum_range nên tham chiếu tới cùng số hàng và cột.

Ở ví dụ sau, công thức trả về tổng doanh thu bán táo hàng ngày ở Bellevue. Tuy nhiên, đối số sum_range (C2:C10) không tương ứng với số hàng và cột ở criteria_range (A2:A12 & B2:B12). Dùng công thức =SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14) sẽ cho lỗi #VALUE.

Giải pháp:

Thay đổi sum_range sang C2:C12 và thử lại công thức.

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