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à một hàm rất hữu ích trong Excel, cho phép bạn tính tổng các giá trị thỏa mãn một hoặc nhiều điều kiện. Bài viết này sẽ giới thiệu cho bạn về hàm SUMIFS là gì, ứng dụng của nó trong Excel, sự khác biệt giữa SUMIF và SUMIFS, cách sử dụng hàm SUMIFS với các ví dụ minh họa, cũng như sự kết hợp của hàm SUMIFS với các hàm khác. Cuối cùng, bài viết cũng sẽ đề cập đến một số lưu ý của hàm SUMIFS trong Excel.

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

Hàm SUMIFS là một hàm toán học và thống kê trong Excel, có tác dụng tính tổng các giá trị trong một vùng dữ liệu (hay còn gọi là mảng) thỏa mãn một hoặc nhiều điều kiện xác định. Hàm sumif trong excel có cùng chức năng nhưng chỉ cho phép bạn nhập một điều kiện duy nhất.

Hàm SUMIFS trong Excel được ứng dụng để:

• Nhanh chóng tính toán tổng dãy số theo các điều kiện cần thiết.

• Tăng sự thuận tiện trong công việc bằng việc kết hợp nhiều dạng hàm trong Excel.

>>> Xem thêm: Cách dùng hàm SUM trong Excel để tính tổng cho người mới bắt đầu

Cách sử dụng hàm sumifs trong excel

>>> Tất cả đều có trong cuốn sách “HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO”

ĐĂNG KÝ MUA NGAY

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

Công thức hàm SUMIFS trong Excel

Công thức của hàm SUMIFS trong Excel được viết như sau

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

Trong đó:

• Sum_range (bắt buộc): Xác định phạm vi ô cần tính tổng

• Criteria_range1 (bắt buộc): Xác định phạm vi được kiểm tra bằng cách sử dụng Criteria1.

• Criteria1 (bắt buộc): Tiêu chí xác định ô nào trong Criteria_range1 sẽ được cộng vào. 

• Criteria_range2, criteria2 (tùy chọn): Các phạm vi bổ sung và các tiêu chí liên kết chúng. Ở đây có thể nhập tối đa 127 cặp phạm vi/tiêu chí.

Lưu ý: Criteria_range1 và Criteria1 là một cặp tìm kiếm, nghĩa là phạm vi được tìm kiếm theo các tiêu chí cụ thể. Giá trị được tìm thấy trong phạm vi tương ứng trong Sum_range sẽ được cộng vào.

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

Để bạn hiểu rõ hơn cách sử dụng hàm SUMIFS trong Excel, Unica sẽ hướng dẫn bạn cách dùng hàm thông qua 3 ví dụ dưới đây:

Ví dụ 1: Ứng dụng hàm SUMIFS để tính tổng tiền các nhân viên ở bộ phận kỹ thuật có lương nhiều hơn 3.000.000 đồng qua bảng sau.

Lưu ý: 

• Để tính giá trị lớn hơn bạn sử dụng dấu >, còn tính giá trị nhỏ hơn bạn sử dụng dấu <.>

• Trong trường hợp bạn muốn tính giá trị bằng, bạn cần nhập thêm giá trị điều kiện.

Bước 1: Mở file Excel có chứa dữ liệu bảng > nhập hàm =SUMIFS(D2:D8,C2:C8,”Kỹ thuật”,D2:D8,”>3000000”) vào ô muốn hiển thị kết quả.

Trong đó:

• Sumifs: Lệnh đặt hàm tính tổng điều kiện.

• D2:D8: Xác định khoảng vùng tham chiếu dữ liệu.

• C2:C8: Vùng tham chiếu dữ liệu cho điều kiện 1.

• “Kỹ thuật”: Điều kiện 1 muốn lấy ở ô tham chiếu.

• D2:D8: Vùng tham chiếu dữ liệu cho điều kiện 2.

• “>3000000”: Điều kiện 2 lấy trong ô tham chiếu.

Ví dụ 1 về cách sử dụng hàm Sumiffs trong Excel

Bước 2: Sau đó, bạn ấn Enter để Excel tự động trả về kết quả.

Kết quả ví dụ 1 về cách sử dụng hàm Sumiffs trong Excel

Ví dụ 2. Ứng dụng hàm SUMIFS tính tổng lương cho nhân viên nữa bán được hơn 100 sản phẩm.

Bước 1: Mở file Excel có chứa dữ liệu bảng > nhập hàm =SUMIFS(F2:F8,C2:C8,”Nữ”,E2:E8,”>100”) vào ô muốn hiển thị kết quả.

Trong đó:

• Sumifs: Lệnh đặt hàm tính tổng điều kiện.

• F2:F8: Xác định khoảng vùng tham chiếu dữ liệu.

• C2:C8: Vùng tham chiếu dữ liệu cho điều kiện 1.

• “Nữ”: Điều kiện 1 muốn lấy ở ô tham chiếu.

• E2:E8: Vùng tham chiếu dữ liệu cho điều kiện 2.

• “>100”: Điều kiện 2 lấy trong ô tham chiếu.

Ví dụ 2 về cách sử dụng hàm Sumiffs trong Excel

Bước 2: Sau đó, bạn ấn Enter để Excel tự động trả về kết quả.

Kết quả ví dụ 2 về cách sử dụng hàm Sumiffs trong Excel

Ví dụ 3. Ứng dụng hàm SUMIFS để tính tổng giá tiền của các loại trái cây trừ trái cam.

Bước 1: Mở file Excel có chứa dữ liệu bảng > nhập hàm =SUMIFS(D2:D7,C2:C7,”>70”,B2:B7,”Cam”) vào ô muốn hiển thị kết quả.

Trong đó:

• Sumifs: Lệnh đặt hàm tính tổng điều kiện.

• D2:D7: Xác định khoảng vùng tham chiếu dữ liệu.

• C2:C7: Vùng tham chiếu dữ liệu cho điều kiện 1.

• “>70”: Điều kiện 1 muốn lấy ở ô tham chiếu.

• B2:B7: Vùng tham chiếu dữ liệu cho điều kiện 2.

• “Cam”: Điều kiện 2 lấy trong ô tham chiếu.

Ví dụ 3 về cách sử dụng hàm Sumiffs trong Excel

Bước 2: Sau đó, bạn ấn Enter để Excel tự động trả về kết quả.

Kết quả ví dụ 3 về cách sử dụng hàm Sumiffs trong Excel

 

>>> Xem thêm: Cách kết hợp hàm sumif và hàm vlookup chi tiết nhất, kèm ví dụ

Những cách thực hành hàm SUMIFS tốt nhất 

Dùng ký tự đại diện 

Trong Criteria 1, 2 bạn có thể sử dụng ký tự đại diện như dấu hỏi chấm (?) và dấu sao (*) để tìm thấy kết quả tương tự nhưng không chính xác.

• Dấu hỏi chấm (?) khớp với ký tự đơn lẻ bất kỳ.

• Dấu sao (*) khớp với chuỗi ký tự bất kỳ.

• Nếu bạn muốn tìm dấu sao hoặc dấu hỏi chấm, bạn cần thêm ký tự ~ (sóng ngã) trước dấu.

Ví dụ: Dễ thấy trong bảng trên có tên bắt đầu bằng “To” và kết thúc bằng một chữ cái có thể thay đổi. Do đó, ám dụng cách trên kết quả hiển thị sẽ là Tom.

Ví dụ dùng ký tự đại diện trong Excel

Sự khác biệt giữa SUMIFS và SUMIF

Sự khác biệt giữa SUMIFS và SUMIF nằm ở thứ tự của các đối số:

• Đối số sum_range là đối số thứ nhất trong SUMIFS.

• Đối sối sum_range là đối số thứ ba trong hàm SUMIF.

Đây chính là nguyên nhân phổ biến nhất gây ra các vấn đề khi sử dụng hàm nay. Nếu có người sao chép và sửa những hàm giống nhau này, bạn cần kiểm tra và đặt lại các đối số theo đúng thứ tự.

Sự khác biệt giữa SUMIFS và SUMIF

Sử dụng cùng một số hàng và cột cho đối số phạm vi 

Đối số Criteria_range trong Excel phải chứa số hàng và số cột bằng với số cột bằng với đối số Sum_range.

Như ở ví dụ 3, ta thấy đối số criteria_range là D2:D7 có số hàng và số cột bằng với đối số Dum_range C2:C7.

Sử dụng cùng một số hàng và cột cho đối số phạm vi

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

• Mỗi ô trong vùng phạm vi sum_range đã được chọn sẽ được cộng nếu thỏa mãn được tất cả các điều kiện đã được xác định chính xác cho ô đó.

• Các ô trong phạm vi sum_range nếu là TRUE sẽ được coi là 1 và nếu là FALSE sẽ được coi là 0.

• Criteria_range cần đảm bảo có cùng kích thước vùng, cùng số hàng, số cột tương tự như sun_range.

•  Trong Criteria, bạn thay thế ký tự đơn bằng dấu hỏi chấm và dấu sao để thay cho chuỗi ký tự. Bạn cần thêm dấu ngã ~ ở trước khi sử dụng dấu hỏi chấm, dấu sao. Giá trị điều kiện ở dạng văn bản sẽ được để trong dấu “.

• Nếu áp dụng nhiều điều kiện cùng một lúc, hàm SUMIFS có thể kết hợp với hàm AND.

• Nếu thấy báo lỗi #VALUE nghĩa là phạm vi mà bạn cung cấp không cùng với số lượng cột, hàng giống với tổng giải ô.

• Các ô phạm vi không bắt buộc phải liền kề với nhau.

• Các văn bản trong điều kiện phải được đặt trong dấu ngoặc kép “. Chẳng hạn như “Cam”, “Quýt”, “>700”.

• Tham chiếu trong ô điều kiện sẽ không được đặt trong dấu ngoặc kém.

• Hàm SUMIFS và SUMIF đều có khả năng xử lý nhiều phạm vi nhưng không có khả năng xử lý một mảng. Nghĩa là trong phạm vi tiêu chí, bạn không thể sử dụng các hàm YEAR bởi kết quả sẽ trả về một mạng. Nếu cần hãy sử dụng hàm Sumproduct.

• Hàm SUMIFS có thứ tự đối số phạm vi tổng ở đầu tiên, còn SUMIF lại có thứ tự đối số ở thứ ba.

Video cách sử dụng của hàm SUMIFS trong Excel

Hướng dẫn cách sử dụng hàm SUMIFS trong Excel

Kết luận

Hàm SUMIFS là một hàm rất hữu ích trong Excel, cho phép bạn tính tổng các giá trị thỏa mãn một hoặc nhiều điều kiện. Bài viết này đã giới thiệu cho bạn về cách sử dụng hàm SUMIFS trong Excel với các ví dụ minh họa, cũng như sự kết hợp của hàm SUMIFS với các hàm khác. Bài viết cũng đã đề cập đến một số lưu ý và hạn chế của hàm SUMIFS. Hy vọng bài viết này sẽ giúp bạn áp dụng hàm SUMIFS hiệu quả trong công việc và học tập của bạn. Cảm ơn bạn đã theo dõi bài viết này.