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

Chúng ta đều đã biết cách tính tổng trong Excel bằng hàm SUM. Vậy còn hàm tính tổng có điều kiện trong Excel thì sao? 

Trong bài viết ngày hôm nay, các bạn hãy cùng Gitiho tìm hiểu về cách sử dụng hàm SUMIFS trong Excel để tính tổng nhiều điều kiện nhé.

Xem thêm: Thành thạo hàm Sumifs và hơn 100 hàm Excel khác nhờ khóa học Excel này

Giới thiệu hàm SUMIFS trong Excel

Như tên gọi của nó, hàm SUMIFS trong Excel là hàm ghép từ hàm SUM và hàm IFS. Do đó, chức năng của hàm cũng chính là sự kết hợp chức năng tính tổng của hàm SUM và chức năng áp dụng nhiều điều kiện cùng lúc cho dữ liệu của hàm IFS. Từ đó, chúng ta hiểu rằng SUMIFS là hàm tính có nhiều điều kiện trong Excel.

Công thức hàm SUMIFS

Nếu bạn đã biết hàm SUMIF trong Excel, bạn sẽ thấy hàm SUMIFS nhiều điều kiện là dạng hàm phức tạp hơn của hàm SUMIF. Cú pháp hàm tính tổng có điều kiện này được thể hiện như sau:

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

Trong đó:

• sum_range: Là phạm vi ô tính cần thực hiện tính tổng dữ liệu. Phạm vi này có thể là một ô duy nhất, một dải ô hoặc các dải ô được đặt tên. Excel sẽ chỉ lọc ra các ô tính có chứa dữ liệu trong phạm vi ô để tính tổng.
• criteria_range1: Là phạm vi ô tính thứ nhất để áp dụng đánh giá theo điều kiện thứ nhất.
• criteria1: Là điều kiện đầu tiên cần phải đáp ứng. Bạn có thể viết tham số điều kiện dưới dạng văn bản, số, hoặc một công thức, một biểu thức hay một tham chiếu ô.
• criteria_range2, criteria2,...: Là các cặp phạm vi-điều kiện khác bạn có thể đưa vào cú pháp hàm. Giới hạn tối đa cho số cặp phạm vi-điều kiện trong công thức SUMIFS là 127.

Một điểm đặc biệt của SUMIFS là việc hàm có thể hoạt động với hàm AND. Khi bạn sử dụng công thức hàm AND trong Excel sẽ chỉ tính tổng các ô tính trong phạm vi sum_range đáp ứng tất cả các điều kiện tương ứng.hàm 

Xem thêm: Bài tập hàm IF nhiều điều kiện kèm hướng dẫn giải chi tiết

Cách dùng hàm SUMIFS cơ bản

Chúng ta sẽ tìm hiểu cách sử dụng SUMIFS làm hàm tính tổng có điều kiện trong Excel qua ví dụ dưới đây nhé.

Giả sử bạn có một bảng tổng hợp các lô hàng trái cây được cung cấp bởi các nhà cung ứng khác nhau. Với bảng này, bạn cần tính tổng số lượng một mặt hàng đến từ một nhà cung ứng xác định, cụ thể hơn là tính sản lượng táo được cung cấp bởi Phát. Để làm được điều này, bạn sẽ cần sử dụng hàm SUMIFS trong Excel.

Hãy cùng bắt đầu thực hành cách tính tổng nhiều điều kiện bằng hàm SUMIFS với bước xác định các tham số trong cú pháp hàm, lần lượt như sau:

• sum_range: Phạm vi cần tính tổng là cột số lượng hàng hóa (Cột C) C2:C9.
• criteria_range1: Phạm vi đáp ứng điều kiện đầu tiên là tên mặt hàng (Cột A) A2:A9
• criteria1: Điều kiện áp dụng cho phạm vi A2:A9 là ô tính có nội dung “Táo”
• criteria_range2: Phạm vi đáp ứng điều kiện thứ hai là tên nhà cùng ứng (Cột B) B2:B9
• criteria2: Điều kiện áp dụng cho phạm vi B2:B9 là ô tính có nội dung “Phát”

Như vậy, công thức hàm SUMIFS nhiều điều kiện của chúng ta sẽ tính tổng các giá trị tại cột C dựa theo hai điều kiện tương ứng: giá trị tại cột A là "Táo" và giá trị tại cột B là "Phát". Công thức chi tiết được thể hiện như sau:

= SUMIFS(C2:C9,A2:A9,“Táo”, B2:B9, “Phát”)

Chúng ta thấy trong bảng chỉ có hai ô tính C4 và C9 đáp ứng đầy đủ các điều kiện của hàm SUMIFS. Chính vì vậy, kết quả hàm tính tổng nhiều điều kiện trả về là tổng của hai giá trị tại ô C4 và C9.

Nếu bạn cần một công thức đơn giản hơn, bạn hoàn toàn có thể viết tham số criteria1 bằng tham chiếu ô F1 chứa giá trị "Táo" và viết tham số criteria2 bằng tham chiếu ô F2. Nhờ đó, bạn có thể áp dụng công thức SUMIFS 2 điều kiện trong Excel khi muốn tính toán lượng trái cây khác đến từ nhà cung ứng khác.

Lúc này, công thức hàm SUMIFS như sau:

=SUMIFS (C2:C9, A2:A9, F1, B2:B9, F2)

Vậy là bạn đã giải quyết được yêu cầu sử dụng hàm tính tổng nhiều điều kiện của đề bài rồi!

Xem thêm: Cách lập báo cáo trên Excel hiệu quả với hàm SUMIFS

Ví dụ hàm SUMIFS với toán tử so sánh

Đề bài

Tính tổng sản lượng các lô hàng hoa quả đến từ nhà cung ứng Mai và đáp ứng điều kiện các lô hàng lớn đó chứa từ 200 sản phẩm trở lên.

Cách làm

Với đề bài này, chúng ta sẽ sử dụng hàm SUMIFS 2 điều kiện trong Excel kết hợp toán tử so sánh "lớn hơn hoặc bằng" (>=) để thỏa mãn điều kiện số lượng sản phẩm trong một lô hàng từ 200 đổ lên. Bên cạnh đó, chúng ta để ý đề bài không yêu cầu tính tổng một mặt hàng cụ thể, nên công thức hàm SUMIFS trong trường hợp này như sau:

=SUMIFS(C2:C9, B2:B9,“Mai”,C2:C9, “>=200”)

Tương tự như công thức SUMIFS trên, giả sử chúng ta cần tính tổng các giá trị trong pham vi C2:C9 thỏa mãn điều kiện nằm trong khoảng 200 và 300, chúng ta sẽ sử dụng hai toán tử "lớn hơn hoặc bằng" (>=) và "nhỏ hơn hoặc bằng" (<=). Công thức hàm SUMIFS như sau:

=SUMIFS(C2:C9,C2:C9,“>=200”,C2:C9,“<=300”)

Lưu ý: Các biểu thức logic chứa toán tử so sánh phải được đặt trong cặp dấu ngoặc kép ("")

Ví dụ hàm SUMIFS với dữ liệu ngày

Đề bài

Tính tổng sản lượng hoa quả từ các lô hàng được nhập kho trong thời gian 7 ngày vừa qua bao gồm ngày hôm nay.

Cách làm

Dựa vào đề bài, chúng ta xác định tham số criteria1 phải thể hiện điều kiện "7 ngày gần nhất tính từ ngày hôm nay", tức ngày bắt đầu tính lô hàng là 7 ngày trước, còn ngày kết thúc là ngày hôm nay. Do đó, chúng ta sử dụng hàm TODAY cho tham số criteria1 và trừ thêm 7 để biểu thị ngày bắt đầu. Tiếp đó, đến tham số criteria2, chúng ta sử dụng hàm TODAY một lần nữa để ấn định ngày kết thúc là ngày hôm nay.

Công thức hàm SUMIFS điều kiện ngày tháng trong trường hợp này như sau:

=SUMIFS(C2:C9,B2:B9,“>=”&TODAY()–7,B2:B9,“<=”&TODAY())

Tương tự với công thức trên, bạn hoàn toàn có thể kết hợp dữ liệu ngày tháng để tính sản lượng hoa quả nhập kho trong khoảng thời gian cụ thể. Giả sử chúng ta cần tính lượng hàng từ ngày 30/06/2021 đến ngày 10/07/2021, công thức hàm SUMIFS điều kiện ngày tháng như sau:

=SUMIFS(C2:C9,B2:B9,“>=6/30/2021”,B2:B9,“<=7/10/2021”)

Lưu ý: Khi áp dụng cả toán tử so sánh và dữ liệu thời gian trong một tham số điều kiện criteria, bạn cần nối các chuỗi bằng ký hiệu "&". Ví dụ: “<=” & TODAY()

Ví dụ hàm SUMIFS với các ô trống

Đề bài

Giả sử ngày đặt hàng được thể hiện tại cột B, ngày nhận hàng được thể hiện tại cột C, số lượng sản phẩm trong một lô hàng đã đặt được thể hiện tại cột D. Tính tổng sản lượng hàng đã đặt nhưng chưa được nhận.

Cách làm

Dựa vào yêu cầu đề bài, chúng ta cần tính tổng các giá trị tại cột D đáp ứng điều kiện ô tương ứng tại cột B (ngày đặt hàng) không phải ô rỗng và ô tương ứng tại cột C (ngày nhận hàng) là ô rỗng. Làm thế nào để thực hiện được điều này? Chúng ta sẽ phân tích từng điều kiện nhé.

Công thức hàm SUMIFS với ô không rỗng

Đầu tiên, chúng ta cần tính tổng các giá trị tại cột D với điều kiện ô tương ứng tại cột B không phải ô rỗng. Chúng ta có thể sử dụng cặp ký tự đặc biệt "<>" để xác định điều kiện này. Giả sử công thức hàm SUMIFS như sau:

=SUMIFS(C2:C9, A2:A9, “<>”, B2:B9, “<>”)

Ngoài ra, một cách khác là  sử dụng kèm hàm SUM để thực hiện thao tác tính tổng trong Excel đối với các ô tính có nội dung ô tính tương ứng khác rỗng:=SUM(C2:C9) – SUMIFS(C2:C9, A2:A9, “”, B2:B9, “”)

Công thức hàm SUMIFS với ô rỗng

Tiếp theo, chúng ta sẽ học cách sử dụng hàm SUMIFS với giá trị tại cột D theo điều kiện ô tương ứng tại cột C là ô rỗng. Cách đơn giản nhất là dùng ký tự đặc biệt "=" để thể hiện các ô trống hoàn toàn. Lúc này, công thức hàm tính tổng nhiều điều kiện SUMIFS như sau:

=SUMIFS(C2:C9,A2:A9,“=”,B2:B9,“=”)

Thay vì ký tự "=", chúng ta có thể sử dụng cặp dấu ngoặc kép "" để biểu diễn điều kiện chuỗi giá trị trong ô tương ứng là chuỗi rỗng.

=SUMIFS(C2:C9,A2:A9,“”,B2:B9,“”)

Bây giờ chúng ta sẽ áp dụng kiến thức này vào trường hợp đề bài. Chúng ta cần tính tổng các giá trị tại cột D với giá trị tương ứng tại cột B là giá trị không rỗng và giá trị tương ứng tại cột C là giá trị rỗng. Công thức hàm SUMIFS của chúng ta như sau:

=SUMIFS(D2:D9,B2:B9,“<>”,C2:C9,“=”)

Xem thêm: Hướng dẫn chi tiết cách đếm số ô trống trong Excel

Tổng kết

Như vậy, chúng ta đã tìm hiểu về cách sử dụng hàm SUMIFS trong các tình huống khác nhau bằng cách kết hợp các toán tử so sánh, dữ liệu thời gian. Với hàm tính tổng có điều kiện này, các bạn có thể tổng hợp và phân tích dữ liệu của mình logic hơn rất nhiều. Để khám phá thêm các hàm tính toán trong Excel, hãy tham khảo các bài viết về chủ đề Excel trên blog tin học văn phòng Gitiho.

Gitiho hy vọng các bạn đã hiểu được cách ứng dụng hàm SUMIFS và chúc các bạn học tập thành công!