Cách tính tổng nhiều điều kiện trong Excel với hàm SUMIF và hàm SUMIFS

Hàm SUMIF và hàm SUMIFS là 2 hàm vô cùng phổ biến mà bất kì ai sử dụng bảng tính Excel cũng cần phải biết. Trong bài viết này, Gitiho sẽ cùng bạn phân biệt 2 hàm tính tổng có điều kiện này cũng như cách sử dụng của chúng. Bên cạnh đó, Gitiho sẽ hướng dẫn bạn cách kết hợp hàm SUMIF và hàm SUMIFS với các hàm khác để giải các bài toán trong Excel.

Xem thêm: Cách viết hàm trong Excel chính xác và chi tiết nhất

Hàm SUMIF và hàm SUMIFS trong Excel

Cú pháp hàm SUMIF và hàm SUMIFS

Hàm SUMIF và hàm SUMIFS là 2 hàm tính tổng có điều kiện vô cùng hữu ích trong Excel. Nhìn qua có vẻ hai hàm này giống nhau, tuy nhiên vẫn có những điểm khác nhau giữa 2 hàm.  Hiểu một cách đơn giản, hàm SUMIF sẽ được sử dụng để tính tổng có điều kiện chỉ dựa trên 1 điều kiện duy nhất; còn hàm SUMIFS được sử dụng để tính tổng có điều kiện dựa trên nhiều điều kiện khác nhau. Vì vậy, cú pháp của hàm SUMIF và hàm SUMIFS cũng có sự khác biệt.

Hàm SUMIF

Hàm SUMIF được dùng để tính tổng có điều kiện với 1 điều kiện duy nhất. Cú pháp của hàm SUMIF như sau:

SUMIF(range, criteria, [sum_range])

Trong đó:

• range (Tham số bắt buộc): Vùng dữ liệu hoặc dải ô để đối chiếu điều kiện.
• criteria (Tham số bắt buộc): Điều kiện cần đáp ứng của bài toán
• sum_range (Tham số tùy chọn): Vùng dữ liệu thỏa mãn điều kiện 
• Lưu ý: Độ dài của hàng và cột ở tham số range và sum_range phải bằng nhau thì hàm SUMIF mới có thể tính toán chính xác

Như bạn đã thấy, hàm SUMIF chỉ cho phép đối chiếu 1 điều kiện duy nhất. Vì vậy, nếu bạn muốn áp dụng nhiều điều kiện hơn cho phép tính thì phải làm thế nào? Bạn sẽ sử dụng nhiều hàm SUMIF liên tiếp chứ? Có một cách dễ dàng và tiện lợi hơn rất nhiều đó! Đó chính là sử dụng hàm SUMIFS.

Hàm SUMIFS

Cú pháp của hàm SUMIFS

Hàm SUMIFS được sử dụng để tính tổng với nhiều điều kiện (từ 2 điều kiện trở lên). Tuy nhiên, hàm SUMIFS chỉ có thể áp dụng với Microsoft Excel phiên bản 2007 trở lên. Đối với phiên bản 2003 trở về trước, nếu muốn tính tổng có điều kiện, ta sẽ sử dụng hàm SUMPRODUCT

Bởi vì chứa nhiều điều kiện hơn trong cùng 1 công thức nên cú pháp của hàm SUMIFS so với hàm SUMIF sẽ phức tạp hơn 1 chút. Cú pháp của hàm SUMIFS như sau:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria],...)

Trong đó:

• sum_range (Tham số bắt buộc): Vùng cần tính tổng. Vùng dữ liệu này có thể là 1 ô hoặc 1 dải ô. 
• criteria_range1 (Tham số bắt buộc): Vùng đầu tiên được đánh giá theo điều kiện liên quan
• criteria1 (Tham số bắt buộc): Điều kiện đầu tiên cần đáp ứng. Điều kiện có thể là một số, biểu thức, tham chiếu ô, văn bản hoặc một hàm khác. 
• criteria_range2, criteria2, …(Tham số không bắt buộc): Các vùng được thêm vào và điều kiện liên quan tới các vùng này, do bạn tùy chọn. Bạn có thể sử dụng tối đa 127 dải / điều kiện trong công thức hàm SUMIFS.
• Lưu ý: 
  • Vùng sum_range và criteria_range1, criteria_range2... phải có độ dài bằng nhau nếu không sẽ xuất hiện lỗi khi tính toán
  • Hàm SUMIFS hoạt động với biểu thức logic với AND, nghĩa là mỗi ô trong đối số sum_range chỉ được tính tổng nếu tất cả các điều kiện criteria_range1,criteria1... đều phải đúng

Ví dụ

Ta có bảng số liệu liệt kê các lô hàng trái cây từ các nhà cung cấp khác nhau. Cột A chứa tên các mặt hàng, cột B chứa tên nhà cung cấp và cột C chứa số lượng của từng mặt hàng. Bạn muốn tìm ra số tiền liên quan đến quả và nhà cung cấp, ví dụ: Tất cả táo được cung cấp bởi Việt Farm.

Ta có công thức sau:

Xem thêm: Cách thay thế lỗi công thức bằng số 0 hoặc khoảng trắng trong Excel

Lưu ý khi sử dụng hàm SUMIF và hàm SUMIFS

Thứ tự của các tham số

Hai hàm tuy có mục đích sử dụng tương tự nhau nhưng cách hoạt động của hai hàm hoàn toàn khác nhau, vì vậy, cú pháp hay thứ tự sắp xếp các tham số trong cú pháp của 2 hàm cũng khác nhau.  Vì vậy, khi nhập hàm hoặc sao chép, chỉnh sửa hàm thì cần đảm bảo giữ đúng thứ tự sắp xếp của các tham số.

Kích thước của tham số sum_range và criteria_range

• Trong hàm SUMIF: Thám số sum_range không nhất thiết phải có cùng kích thước với tham số range, chỉ cần có ô phía trên bên trái.
• Trong hàm SUMIFS: Mỗi criteria_range phải chứa cùng một số hàng và cột như tham số sum_range.
• Chỉ có các ô chỉ chứa số mới được tính tổng; Còn giá trị ô trống và giá trị văn bản thì sẽ bị bỏ qua. Vì vậy cần định dạng số chính xác cho vùng cần tính tổng. 

Xem thêm: Hướng dẫn cách xử lý lỗi định dạng số trong Excel đơn giản nhanh chóng

Lưu ý khi viết điều kiện trong hàm SUMIF và hàm SUMIFS

• Với các criteria dạng text truyền trực tiếp cần để trong dấu  " "
• Với các criteria dạng so sánh cần đặt ">= criteria " hoặc truyền điều kiện dạng ">="&criteria 

Kết hợp hàm SUMIF và hàm SUMIFS với các hàm và chức năng khác trong Excel

Hàm SUMIF và hàm SUMIFS không chỉ được sử dụng đơn lẻ mà còn có thể kết hợp với các hàm, chức năng khác để giải các bài toán từ đơn giản tới phức tạp trong Excel. Để hiểu rõ hơn về từng cách kết hợp, hãy cùng mình xét các cách kết hợp trong các ví dụ cụ thể nhé!

Hàm SUMIFS

Kết hợp hàm SUMIFS với các phép so sánh

Bạn có thể sử dụng các phép so sánh như lớn hơn (>), nhỏ hơn (<), lớn hơn hoặc bằng (>=), nhỏ hơn hoặc bằng (<=) để làm điều kiện chohamf SUMIFS. Một lưu ý nhỏ là khi sử dụng các phép so sánh kết hợp trong hàm SUMIFS đó là các biểu thức so sánh này cần đặt trong dấu ngoặc kép " ". Xét ví dụ sau:

 

Kết hợp hàm SUMIFS với các hàm thời gian

Ví dụ, bạn cần tính tổng thời gian làm việc của một nhân viên để dễ dàng tính lương, hay tổng thời gian hoạt động của một dự án nào đó..thì nhất định cần sử dụng tới sự kết hợp giữa hàm SUMIFS và các hàm thời gian. Lưu ý: Khi bạn sử dụng một hàm Excel khác cùng với các phép so sánh, bạn phải sử dụng ký hiệu và (&) để nối với 1 chuỗi, ví dụ “<=” & TODAY (). Xem ví dụ sau:

 

Xem thêm: Công thức tính ngày công bằng các hàm trong Microsoft Excel

Hàm SUMIF

Kết hợp 2 hàm SUMIF với nhau

Giải pháp đơn giản nhất là tính tổng các kết quả trả về bởi một số hàm SUMIF. Ví dụ, ta muốn tính tổng của tổng có điều kiện của 2 người khác nhau, ta sẽ sử dụng kết hợp 2 hàm SUMIF

 

Kết hợp hàm SUMIF và hàm SUMPRODUCT

Nếu bạn muốn liệt kê các điều kiện thay vì đặt chúng hết vào trong công thức thì bạn có thể sử dụng kết hợp hàm SUMIF và hàm SUMPRODUCT để nhân nhiều thành phần trong các mảng  xác định rồi trả lại tổng số lượng của các sản phẩm đó.

 

Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ!

Tổng kết

Trong bài viết này, Gitiho đã cùng bạn phân biệt sự khác biệt giữa 2 hàm tính tổng có điều kiện trong Excel là hàm SUMIF và hàm SUMIFS. Bên cạnh đó, chúng ta đã cùng nhau xem một số ví dụ về cách kết hợp hàm SUMIF và hàm SUMIFS với các hàm và chức năng khác để giải những bài toán trong Excel. Bạn có thể tải file thực hành ở cuối bài.

Chúc bạn học tốt!