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 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 ý và hạn chế của hàm sumifs trong excel.

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

Hàm SUMIFS là gì? Hàm sumif trong excel có tác dụng gì?

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.

Công thức của hàm SUMIFS như sau:

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

Trong đó:

- sum_range: là vùng dữ liệu chứa các giá trị cần tính tổng.

- criteria_range1: là vùng dữ liệu chứa các giá trị để so sánh với điều kiện 1.

- criteria1: là điều kiện 1 để lọc các giá trị trong sum_range.

- [criteria_range2, criteria2]: là các vùng dữ liệu và điều kiện tiếp theo để lọc các giá trị trong sum_range. Bạn có thể nhập tối đa 127 cặp vùng dữ liệu và điều kiện.

>>> 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. Hình 1

Ứng dụng của hàm SUMIFS trong Excel

Hàm SUMIFS có rất nhiều ứng dụng trong Excel, đặc biệt là khi bạn muốn tính tổng các giá trị theo nhiều tiêu chí khác nhau. Ví dụ:

- Người dùng có một bảng dữ liệu về doanh số bán hàng của các sản phẩm theo từng tháng và từng khu vực. Bạn muốn tính tổng doanh số bán hàng của sản phẩm A trong tháng 1 tại khu vực Bắc.

- Người dùng có một bảng dữ liệu về điểm thi của các sinh viên theo từng môn học và từng lớp. Bạn muốn tính tổng điểm thi của các sinh viên thuộc lớp 10A và có điểm thi toán lớn hơn hoặc bằng 8.

Hàm SUMIFS có rất nhiều ứng dụng trong Excel

- Người dùng có một bảng dữ liệu về chi tiêu cá nhân của bạn theo từng ngày và từng loại chi tiêu. Bạn muốn tính tổng chi tiêu của bạn trong tháng 9 cho các loại chi tiêu là ăn uống, giải trí và du lịch.

Trong những trường hợp này, bạn có thể sử dụng hàm SUMIFS để tính tổng các giá trị theo nhiều điều kiện một cách nhanh chóng và chính xác.

>>> 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

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

Như đã nói ở trên, hàm SUMIF và hàm SUMIFS đều có chức năng tính tổng các giá trị thỏa mãn điều kiện. Tuy nhiên, có một số sự khác biệt quan trọng giữa hai hàm này:

- Hàm SUMIF chỉ cho phép bạn nhập một điều kiện duy nhất, trong khi hàm SUMIFS cho phép bạn nhập nhiều điều kiện.

- Thứ tự của các tham số trong công thức của hai hàm cũng khác nhau. Hàm SUMIF có công thức là =SUMIF(range, criteria, [sum_range]), trong đó range là vùng dữ liệu chứa các giá trị để so sánh với điều kiện, criteria là điều kiện để lọc các giá trị, [sum_range] là vùng dữ liệu chứa các giá trị cần tính tổng. Nếu bạn không nhập [sum_range], hàm SUMIF sẽ tính tổng các giá trị trong range. Hàm SUMIFS có công thức là =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …), trong đó sum_range là vùng dữ liệu chứa các giá trị cần tính tổng, criteria_range1 là vùng dữ liệu chứa các giá trị để so sánh với điều kiện 1, criteria1 là điều kiện 1 để lọc các giá trị trong sum_range, và [criteria_range2, criteria2] là các vùng dữ liệu và điều kiện tiếp theo để lọc các giá trị trong sum_range.

- Hàm SUMIF có thể sử dụng toán tử đại diện (* và ?) trong điều kiện để tìm kiếm các giá trị chứa ký tự đặc biệt hoặc bất kỳ ký tự nào. Ví dụ: =SUMIF(A1:A10,“*h”,B1:B10) sẽ tính tổng các giá trị trong B1:B10 tương ứng với các giá trị trong A1:A10 kết thúc bằng chữ h. Hàm SUMIFS không hỗ trợ toán tử đại diện này.

Sự khác biệt của hàm SUMIF và hàm SUMIFS

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

Để hiểu thêm về cách sử dụng hàm sumifs trong Excel, mời bạn cùng theo dõi 6 ví dụ dưới đây:

1. Cách dùng hàm SUMIFS tính tổng có điều kiện

Bạn có thể dùng hàm SUMIFS để tính toán với một điều kiện trong excel. Để hiểu hơn, mời bạn theo dõi ví dụ dưới đây:

Ví dụ: Cho bảng dữ liệu 1:

Yêu cầu: Tính tổng các loại quả được xếp loại A trong bảng 1.

Cách thực hiện:

- Bước 1: Nhập công thức =SUMIFS(D3:D7;C3:C7;"A") vào ô B9.

Nhập công thức vào ô B9

- Bước 2: Nhấn enter sẽ thu được kết quả là 48, tức là tổng các loại quả loại A trong bảng 1 là 48kg.

Kết quả sau khi nhập công thức

2. Cách dùng hàm SUMIFS nhiều điều kiện

Muốn tính toán với những bảng dữ liệu từ 2 điều kiện trở lên, bạn có thể sử dụng hàm SUMIFS.

Ví dụ: Cho bảng dữ liệu 2:

Yêu cầu: Tính tổng tiền lương của nhân viên với điều kiện là mức lương cao hơn 6.500.000 VNĐ.

Cách thực hiện:

- Bước 1: Ở ô B20, nhập công thức sau:

=SUMIFS(D16:D19;C16:C19;"Nhân viên";D16:D19;">6500000").

Nhập công thức vào ô B20

- Bước 2: Nhấn enter, sẽ thu được kết quả là 15.700.000 VNĐ. Tức là tổng tiền lương của nhân viên trong bảng số 2 mà có mức lương cao hơn 6.500.000 VNĐ là 15.700.000 VNĐ.

Kết quả sau khi tính toán

>>> 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ụ

3. Công thức SUMIFS với toán tử so sánh

Ngoài việc sử dụng các giá trị cụ thể, bạn cũng có thể sử dụng các toán tử so sánh trong các điều kiện của hàm SUMIFS. Các toán tử so sánh bao gồm:

- =: bằng

- <>: khác

- >: lớn hơn

- <: nhỏ hơn

- >=: lớn hơn hoặc bằng

- <=: nhỏ hơn hoặc bằng

Ví dụ: Cho bảng dữ liệu 2:

Yêu cầu: Tính tổng tiền lương của nhân viên với điều kiện là mức lương cao hơn hoặc bằng 6.500.000 VNĐ.

Cách thực hiện:

- Bước 1: Viết công thức SUMIFS vào ô B21:

=SUMIFS(D16:D19;C16:C19;"Nhân viên";D16:D19;">=6500000").

Nhập công thức vào B21

- Bước 2: Nhấn enter, thu được kết quả là 22.200.000 VNĐ. Tức là tổng tiền lương của nhân viên có mức lương cao hơn hoặc bằng 6.500.000 VNĐ là 22.200.000 VNĐ.

Kết quả sau khi nhập công thức

4. Sử dụng hàm sumifs trong excel với ngày

Khi SUMIFS kết hợp với ngày tháng sẽ cho ra công thức tính toán phục vụ nhiều mục đích như tính ngày lương, tính điểm thi của học sinh, sinh viên,...

Ví dụ: Cho bảng dữ liệu 3:

Yêu cầu: Tính tổng tiền lương của nhân viên từ ngày 15-21/9/2023.

Cách thực hiện:

- Bước 1: Ở ô C47, bạn nhập công thức:

=SUMIFS(E41:E45;F41:F45;">=15/9/2023";F41:F45;"<=21/9/2023").

Nhập công thức vào ô C47

- Bước 2: Bấm enter sẽ thu được kết quả là 69, tức là tổng tiền lương của nhân viên từ ngày 15-21/9/2023 là 69 triệu đồng.

Kết quả sau khi nhập công thức

5. Hàm SUMIFS với ô trống và không trống

Nếu bạn muốn tính tổng lương của các nhân viên có hoặc không có tên, bạn có thể sử dụng hàm SUMIFS với ô trống và không trống. Để làm được điều này, bạn cần sử dụng toán tử so sánh là “” (dấu ngoặc kép liền nhau) để đại diện cho ô trống, và toán tử so sánh là “<>” (dấu nhỏ hơn kết hợp với dấu lớn hơn) để đại diện cho ô không trống.

Ví dụ: Cho bảng dữ liệu 4:

Yêu cầu: Tính tổng tiền lương của những nhân viên có tên trong bảng 4.

Cách thực hiện:

- Bước 1: Ở ô C64, bạn nhập công thức tính sau:

=SUMIFS(E57:E62;C57:C62;"<>").

Nhập công thức

- Bước 2: Nhấn enter, kết quả thu được là 54, tức là tổng tiền lương của những người có tên trong bảng 4 là 54 triệu đồng.

Kết quả thu được là 54 triệu đồng

6. Cách dụng hàm sumifs giữa 2 sheet

Hàm SUMIFS không chỉ cho phép bạn tính toán trong cùng 1 sheet, mà bạn còn có thể dùng để tính toán giữa 2 sheet khác nhau.

Ví dụ: Cho bảng dữ liệu 5 và 6 như hình:

Yêu cầu: Tính tổng tiền các loại trái cây được xếp loại A trong bảng.

Cách dùng hàm sumif nhiều sheet trong excel:

- Bước 1: Ở ô B8 thuộc sheet3, bạn viết công thức tính:

=SUMIFS(C3:C6;Sheet2!C3:C6;"A").

Nhập công thức tính

- Bước 2: Nhấn enter sẽ thu được kết quả là 42, tức là tổng số tiền của loại quả được xếp loại A trong ví dụ là 42 ngàn đồng.

Thu được kết quả là 42 ngàn đồng

Lưu ý khi dùng hàm tính tổng có điều kiện trong nhiều sheet

Khi sử dụng hàm SUMIFS trong Excel, bạn cần chú ý đến một số điểm sau:

- Hàm SUMIFS chỉ hoạt động với các giá trị số, không hoạt động với các giá trị văn bản hoặc logic.

- Hàm SUMIFS sẽ trả về giá trị lỗi #VALUE! nếu sum_range không có cùng kích thước với các criteria_range.

- Hàm SUMIFS sẽ trả về giá trị 0 nếu không có giá trị nào thỏa mãn các điều kiện.

- Hàm SUMIFS sẽ áp dụng toán tử VÀ (AND) cho các điều kiện, tức là chỉ tính tổng các giá trị thỏa mãn tất cả các điều kiện. Nếu bạn muốn áp dụng toán tử HOẶC (OR) cho các điều kiện, bạn có thể sử dụng hàm SUM kết hợp với hàm SUMIFS với đối số mảng như đã nói ở trên.

- Hàm SUMIFS không hỗ trợ toán tử đại diện (* và ?) trong các điều kiện. Nếu bạn muốn sử dụng toán tử đại diện, bạn có thể sử dụng hàm SUMIF hoặc hàm SUMIFS kết hợp với hàm COUNTIF.

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

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

Hàm SUMIFS là một hàm rất mạnh mẽ và linh hoạt trong Excel, nhưng cũng có một số hạn chế mà bạn cần biết:

- Hàm SUMIFS chỉ cho phép bạn nhập tối đa 127 cặp vùng dữ liệu và điều kiện. Nếu bạn muốn nhập nhiều hơn, bạn có thể sử dụng các công thức khác như hàm DSUM hoặc hàm SUMPRODUCT.

- Hàm SUMIFS chỉ hoạt động với các giá trị số, không hoạt động với các giá trị văn bản hoặc logic. Nếu bạn muốn tính tổng các giá trị văn bản hoặc logic, bạn có thể sử dụng các công thức khác như hàm CONCATENATE hoặc hàm COUNTIFS.

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.