Cách dùng hàm sumifs trong excel cho dân văn phòng

Bạn đang cần tính tổng với nhiều điều kiện thì bạn dùng tới hàm Sumifs. Nhưng bạn không biết cách sử dụng hàm Sumifs. Bài viết dưới đây sẽ hướng dẫn bạn cách sử dụng hàm sumifs trong excel để tính tổng với nhiều điều kiện.

HÀM SUMIFS – CÔNG THỨC VÀ CÁCH SỬ DỤNG:

Bạn sử dụng hàm SUMIFS trong Excel để tính tổng có giá trị và các giá trị dựa trên nhiều điều kiện. Hàm SUMIFS đã được Microsoft giới thiệu trong Excel 2007, vì vậy bạn hoàn toàn có thể sử dụng nó trong tất cả các phiên bản của Excel như phiên bản 2013, 2010 và 2007.

So với hàm SUMIF, thì công thức hàm SUMIFS phức tạp hơn một chút:

3 tham số đầu tiên là bắt buộc, các range được bổ sung với các điều kiện liên quan tới chúng thì được tùy chọn.

• – có ý nghĩa là một hoặc nhiều ô tính tổng, mang tính bắt buộc. Đây hoàn toàn có thể là một ô duy nhất, một dải ô hoặc dải có tên. Chỉ có các ô chỉ chứa dữ liệu số mới được tính tổng; Còn những giá trị ô trống và giá trị văn bản thì sẽ bị bỏ qua, không được tính.

• range đầu tiên được đánh giá theo các điều kiện liên quan, mang tính chất bắt buộc phải có.

• – đây là điều kiện đầu tiên phải được đáp ứng, là đối số bắt buộc phải có. Bạn hoàn toàn có thể cung cấp các điều kiện dưới dạng một số, biểu thức logic, hay tham chiếu ô, văn bản hoặc một hàm Excel khác. Ví dụ bạn hoàn toàn có thể sử dụng các điều kiện như 10, “> = 10”, A1, “cherries” hoặc TODAY ().

• … – đây là các dải có thể được thêm vào và điều kiện liên quan tới các dải này, do bạn tùy chọn. Bạn hoàn toàn có thể sử dụng tối đa 127 dải hoặc điều kiện trong công thức SUMIFS.

Chú ý: Hàm SUMIFS chỉ được hoạt động với biểu thức logic với AND, nghĩa là mỗi ô trong tham số sum_range chỉ được tính tổng nếu tất cả các điều kiện được chỉ định là đúng cho ô đó.

Và bây giờ, bạn hãy xem hàm SUMIFS hoạt động với hai điều kiện như thế nào. Giả sử bạn có một bảng liệt kê các lô hàng trái cây từ các nhà cung cấp khác nhau với số lượng bạn nhập. Bạn có các dữ liệu như: tên quả trong cột A, tên của nhà cung cấp trong cột B và số lượng trong cột C. Bạn muốn tìm ra số tiền liên quan đến một loại quả của các nhà cung cấp, ví dụ: Tất cả táo (apples) được cung cấp bởi Pete.

Khi bạn mới tiếp xúc, bạn nên bắt đầu với những thứ đơn giản nhất. Vì vậy, để bắt đầu, bạn hãy xác định tất cả các đối số cho công thức hàm SUMIFS:

Bây giờ bạn tập hợp các thông số trên, và bạn sẽ nhận được công thức hàm SUMIFS như sau:

Để công việc chỉnh sửa công thức được đơn giản hơn, bạn hoàn toàn có thể thay thế các dữ liệu văn bản “apples” và “Pete” bằng các tham chiếu ô. Trong trường hợp này, bạn hoàn toàn không phải thay đổi công thức để tính toán lượng trái cây khác từ một nhà cung cấp khác nhau:

=

LÀM THẾ NÀO ĐỂ SỬ DỤNG TRONG EXCEL – VÍ DỤ CÔNG THỨC

Bạn hoàn toàn có thể sử dụng Excel SUMIFS với nhiều điều kiện thể hiện bằng số, ngày, hay biểu thức logic, và các hàm Excel khác.

VÍ DỤ 1. CÔNG THỨC VỚI TOÁN TỬ SO SÁNH

Trong bảng cung cấp trái cây của một siêu thị dưới đây, giả sử, bạn muốn tính tổng tất cả các lượng hàng đã được Mike cung cấp cùng với số lượng từ 200 trở lên. Để làm điều này, bạn hoàn toàn sử dụng toán tử so sánh “lớn hơn hoặc bằng” (> =) trong các điều kiện 2 và nhận được công thức hàm SUMIFS sau:

Lưu ý: Trong các công thức hàm SUMIFS, các biểu thức logic với các toán tử so sánh phải luôn luôn được đặt trong một dấu nháy kép (“”).

Ví dụ: Bạn hãy tính giá trị trả về giá trị tổng của tất cả các giá trị trong dải ô C2: C9 mà lớn hơn hoặc bằng 200 và nhỏ hơn hoặc bằng 300.

VÍ DỤ 2. SỬ DỤNG CÔNG THỨC SUMIFS VỚI NGÀY

Trong trường hợp bạn muốn tính được tổng các giá trị với nhiều điều kiện dựa trên ngày hiện tại, bạn hãy sử dụng hàm TODAY () trong điều kiện của hàm SUMIFS của bạn, như được trình bày bên dưới. Công thức dưới đây tính tổng giá trị trong cột D nếu ngày tương ứng trong cột C rơi vào khoảng thời gian là 7 ngày vừa qua, đã có bao gồm ngày hôm nay:

Chú ý: Khi bạn sử dụng một hàm Excel khác cùng với toán tử logic trong các điều kiện, bạn phải sử dụng các ký hiệu và (&) để nối với 1 chuỗi ký tự, ví dụ “<=” & TODAY ().

Tương tự như vậy, bạn hoàn toàn có thể sử dụng hàm SUMIF để tính tổng các giá trị trong một dải có dữ liệu là ngày xác định. Ví dụ: công thức SUMIFS sau sẽ thêm các giá trị trong dải ô C2: C9 nếu ngày trong cột B rơi trong khoảng giữa ngày 1 tháng 10 năm 2014 với ngày 31 tháng 10 năm 2014, như sau:

= SUMIFS (C2: C9, B2: B9, “> = 10/1/2014”, B2: B9, “<= 10/31/2014”)

Kết quả tương tự hoàn toàn có thể đạt được bằng cách tính toán sự khác biệt của hai hàm SUMIF, như đã được minh họa trong ví dụ này – Cách sử dụng hàm SUMIF để tính tổng các giá trị trong một dải ngày xác định. Tuy nhiên, bạn cũng thấy rằng công thức của hàm SUMIFS là dễ dàng hơn và dễ hiểu hơn nhiều, phải không nào?

VÍ DỤ 3. HÀM SUMIFS VỚI Ô TRỐNG VÀ KHÔNG TRỐNG

Khi bạn cần phân tích báo cáo và dữ liệu khác, bạn thường cần phải tính tổng các giá trị tương ứng với ô trống hoặc không trống.

Điều kiện
Mô tả
Công thức ví dụ

Những ô trống

“=”

Tính tổng các giá trị mà có ô trống tương ứng (hoàn toàn không chứa dữ liệu – có nghĩa là không công thức, và chuỗi có 0 kí tự)

=SUMIFS(C2:C10, A2:A10, “=”, B2:B10, “=”)

Tính tổng giá trị trong dải ô C2:C10 nếu các ô tương ứng với nó trong cột A và B là ô hoàn toàn trống.

“”

Tính tổng các giá trị tương ứng với những ô trắng “nhận định trực quan”, bao gồm các giá trị chứa trong các chuỗi rỗng được trả về bởi một số hàm Excel khác (ví dụ: ô có công thức như = “”).

=SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)

Tính tổng các giá trị có trong dải ô C2:C10 (có cùng các điều kiện) như công thức ở trên, nhưng có bao gồm các chuỗi trống.

Những ô không trống

“<>”

Tính tổng các giá trị mà có các giá trị tương ứng với nó là các ô không trống, và có bao gồm chuỗi có chiều dài = 0

=SUMIFS(C2:C10, A2:A10, “<>”, B2:B10, “<>”)

Tính tổng giá trị trong dải ô C2:C10 nếu các ô tương ứng với nó trong cột A và B không phải là ô trống, trong đó bao gồm các ô với chuỗi trống.

SUM-SUMIF

hay

SUM / LEN

Tính tổng các giá trị mà có các giá trị tương ứng với nó là các ô không trống, và không bao gồm chuỗi có chiều dài = 0

=SUM(C2:C10) – SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)

=SUM((C2:C10) * (LEN(A2:A10)>0)*(LEN(B2:B10)>0))

Tính tổng giá trị trong dải ô C2:C10 nếu các ô tương ứng với nó nằm trong cột A và B không là ô trống, và không bao gồm các ô với các chuỗi trống.

Và bây giờ, hãy cùng xem cách bạn hoàn toàn có thể sử dụng công thức SUMIFS với điều kiện “trống” hoặc “không trống” trên dữ liệu thực như thế nào:

Giả sử bạn đang có ngày đặt hàng trong cột B, ngày giao hàng trong cột C và số lượng trong cột D. Làm thế nào để bạn có thể tính được tổng số sản phẩm chưa được giao? Nghĩa là bạn cần phải biết tổng các giá trị tương ứng với các ô không rỗng trong cột B và các ô rỗng trong cột C.

Giải pháp là bạn sử dụng công thức SUMIFS với 2 điều kiện:

= SUMIFS (D2: D10, B2: B10, “<>”, C2: C10, “=”)

Rate this post