[Kách Hay] Hàm SUMIFS, cách dùng hàm tính tổng nhiều điều kiện trong Excel mới nhất 2023

Hàm SUMIFS, cách dùng hàm tính tổng nhiều điều kiện trong Excel – Kách Hay .Com đã tổng hợp thông tin từ nhiều nguồn, giúp bạn có góc nhìn đa chiều hơn. Nào chúng ta bắt đầu thôi

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 cực kỳ hữu ích khi bạn cần tính tổng nhiều điều kiện. Dưới đây là chi tiết về cách sử dụng hàm Sumifs trong Microsoft Excel .

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

• MS Excel – Bài 5: Hàm và Công thức Excel
• Cách kết hợp hàm sumif và hàm vlookup trong Excel
• 3 cách tính tổng trong Excel
• Cách sửa lỗi hàm SUM không tính tổng trong Excel

Mục lục của bài viết

• Cú pháp hàm SUM trong Excel
• Lưu ý khi sử dụng hàm SUM trong Excel
• Hướng dẫn sử dụng hàm SUM trong Excel

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

Sử dụng các phạm vi đã đặt tên với hàm SUMIFS .

Ví dụ về hàm SUM trong Excel.

• Hạn chế của hàm SUM .

Cú pháp hàm SUM trong Excel

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

Ở trong:

• Sum_range: là các ô trong bảng dữ liệu cần tính tổng, giá trị trống và text được bỏ qua, tham số bắt buộc.
• Criteria_range1: Phạm vi được kiểm tra bởi điều kiện Criteria1 là một 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 hoặc tham chiếu ô để xác định ô nào trong Criteria_Range1 được tính tổng, cũng là một giá trị bắt buộc.
• Criteria_range2,criteria2,…: phạm vi và điều kiện bổ sung tùy chọn, tối đa 127 cặp tiêu chí_range, điều kiện.

Lưu ý khi sử dụng hàm SUM trong Excel

• Đối với mỗi ô trong phạm vi, phạm vi sum_range được tính tổng nếu tất cả các điều kiện được chỉ định tương ứng đều đúng đối với ô đó.
• Các ô trong sum_range chứa TRUE được coi là 1, các ô chứa FALSE được coi là 0.
• Mỗi phạm vi tiêu chí phải có cùng kích thước lựa chọn với phạm vi tổng, phạm vi tiêu chí và phạm vi tổng phải có cùng số hàng và cùng số cột.
• Tiêu chí có thể sử dụng dấu chấm hỏi (?) thay cho ký tự đơn hoặc dấu hoa thị (*) thay cho chuỗi. Nếu điều kiện là dấu chấm hỏi hoặc dấu hoa thị, bạn phải thêm dấu ~ vào trước, giá trị của điều kiện là văn bản trong phần mở rộng “.
• Nhiều điều kiện được áp dụng với logic AND, tức là tiêu chí 1 VÀ tiêu chí 2, 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 phạm vi, nhưng các phạm vi không nhất thiết phải liền kề nhau. Nếu bạn chỉ định 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 đó có nghĩa là bạn không thể sử dụng các hàm khác như YEAR cho 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 chức năng TÓM TẮT.
• Thứ tự của các đối số giữa các hàm SUMIFS và SUMIFS là khác nhau. Phạm vi tổng là đối số đầu tiên trong SUMFS nhưng là đối số thứ ba trong SUMIF.

Hướng dẫn sử dụng hàm SUM trong Excel

Sử dụng 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 các kết quả 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 nào, có thể được sử dụng sau, trước hoặc với các 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 SUM:

• N* – Ngụ ý tất cả các ô trong phạm vi bắt đầu bằng N
• *N – Hàm ý 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 (?) – So khớ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í. “?” Một ký tự duy nhất được thay thế ở đây. N?r khớp với North, Nor, v.v. Tuy nhiên, tên bị bỏ qua.

Đ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ử dụng hàm SUMIFS trong Excel tính tổng nhiều điều kiện có ví dụ

Hàm SUMIFS trong Excel là hàm tính tổng có nhiều điều kiện khác nhau dễ dàng giúp bạn làm việc hiệu quả hơn và bạn chưa biết cách sử dụng hàm này. Hãy cùng theo dõi bài viết sau để hiểu rõ hơn về hàm SUMIFS cũng như cách sử dụng hàm này nhé.

Hướng dẫn được thực hiện trên Laptop hệ điều hành Windows 10 với phiên bản Excel 2013. Bạn cũng có thể thực hiện tương tự trên các sản phẩm Excel thông dụng hiện nay và trên các dòng laptop có cùng hệ điều hành hay dòng MacOS có cùng thao tác khác.

I. Hàm SUMIFS là gì? Ứng dụng của hàm SUMIFS trong Excel

Hàm SUMIFS là hàm tính tổng các ô trong vùng, phạm vi lựa chọn thỏa mãn một hay nhiều điều kiện.

Ứng dụng hàm SUMIF trong Excel.

• Tính được nhanh chóng tổng dãy số theo các điều kiện cần thiết.
• Kết hợp nhiều dạng hàm trong Excel, thuận tiện trong công việc.

II. Cách sử dụng hàm SUMIFS trong Excel

1. Cú pháp hàm

Trong bảng tính Excel bạn nhập hàm =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…) vào ô bảng tính cần hiển thị kết quả.

Trong đó:

• Sum_range: :Là các ô cần tính tổng bao gồm các số hoặc tên, phạm vi hoặc tham chiếu ô có chứa các số.
• Criteria_range1: Là phạm vi cần được đánh giá bằng điều kiện.
• Criteria1: Là điều kiện dưới dạng một số, biểu thức, tham chiếu ô.
• Criteria_range2,criteria2,… : tùy chọn các phạm vi và điều kiện bổ sung.

2. Cách sử dụng

Để hiểu rõ hơn về hàm SUMIFS ở đây mình hướng dẫn cụ thể qua các ví dụ minh họa.

Ví dụ 1: Hãy sử dụng hàm SUMIFS để tính tổng giá tiền của các nhân viên ở bộ phận kỹ thuật có lương > 3.000.000 trong bảng sau:

Lưu ý:

• Khi bạn muốn tính giá trị nhỏ hơn chỉ cần thay dấu > thành dấu <
• Khi bạn muốn tính giá trị bằng bạn chỉ cần nhập giá trị điều kiện

Bước 1: Trong bảng tính Excel bạn nhập hàm =SUMIFS(D2:D8,C2:C8,”Kỹ thuật”,D2:D8,”>3000000″) vào ô tham chiếu muốn hiển thị kết quả.

Giải thích hàm:

• SUMIFS: Là lệnh hàm tính tổng điều kiện.
• D2:D8: Là vùng tham chiếu dữ liệu lấy kết quả.
• C2:C8: Là vùng tham chiếu dữ liệu cho điều kiện 1.
• “Kỹ thuật”: Là điều kiện 1 muốn lấy ở trong ô tham chiếu.
• D2:D8: Là vùng tham chiếu dữ liệu cho điều kiện 2.
• “>3000000”: Là điều kiện 2 muốn lấy trong ô tham chiếu.

Nhập hàm =SUMIFS vào ô bảng tính giống trong hình

Bước 2: Nhấn phím Enter để hiển thị kết quả.

Nhấn Enter để hiển thị kết quả.

Ví dụ 2: Hãy sử dụng hàm SUMIFS để tính tổng tiền lương của nhân viên nữ bán được > 100 sản phẩm trong bảng sau:

Bước 1: Trong bảng tính Excel bạn nhập hàm =SUMIFS(F2:F8,C2:C8,”Nữ”,E2:E8,”>100″) vào ô tham chiếu muốn hiển thị kết quả.

Giải thích hàm:

• SUMIFS: Là lệnh hàm tính tổng điều kiện.
• F2:F8: Là vùng tham chiếu dữ liệu lấy kết quả.
• C2:C8: Là vùng tham chiếu dữ liệu cho điều kiện 1.
• “Nữ”: Là điều kiện 1 muốn lấy ở trong ô tham chiếu.
• E2:E8: Là vùng tham chiếu dữ liệu cho điều kiện 2.
• “>100”: Là điều kiện 2 muốn lấy trong ô tham chiếu.

Nhập hàm =SUMIFS vào ô bảng tính giống trong hình

Bước 2: Nhấn phím Enter để hiển thị kết quả.

Nhấn Enter để hiển thị kết quả.

Ví dụ 3: Hãy sử dụng hàm SUMIFS để tính tổng giá tiền của tất cả trái cây trừ trái Cam trong bảng sau:

Bước 1: Trong bảng tính Excel bạn nhập hàm =SUMIFS(D2:D7,C2:C7,”>70″,B2:B7,”<>Cam”) vào ô tham chiếu muốn hiển thị kết quả.

Giải thích hàm:

• SUMIFS: Là lệnh hàm tính tổng điều kiện.
• D2:D7: Là vùng tham chiếu dữ liệu lấy kết quả.
• C2:C7: Là vùng tham chiếu dữ liệu cho điều kiện 1.
• “>70”: Là điều kiện 1 muốn lấy ở trong ô tham chiếu.
• B2:B7: Là vùng tham chiếu dữ liệu cho điều kiện 2.
• “<>Cam”: Là điều kiện 2 nên đi vào ô tham chiếu.

Nhập chức năng như được hiển thị trong ô của bảng.

Bước 2: Nhấn Enter để xem kết quả.

Nhấn Enter để xem kết quả.

 

---

 

Video [Kách Hay] Hàm SUMIFS, cách dùng hàm tính tổng nhiều điều kiện trong Excel mới nhất 2023