Cách sử dụng hàm SUMIFS và SUMIF với nhiều điều kiện

Trong bài viết này, Học Excel Online sẽ giải thích sự khác biệt giữa các hàm SUMIF và SUMIFS trong excel theo cú pháp và cách sử dụng của chúng, bên cạnh đó, cũng cung cấp một số ví dụ về công thức để tính tổng các giá trị có nhiều điều kiện đồng thời xảy ra (AND) hoặc tất cả không đồng thời xảy ra (OR) trong phiên bản Excel 2013, 2010, 2007, 2003 và trước nữa.

Hàm SUMIF – cú pháp và cách sử dụng:

Hàm SUMIF được sử dụng để tính tổng có điều kiện, dựa trên một điều kiện. Chúng tôi đã thảo luận về cú pháp của nó một cách chi tiết trong bài viết trước, vì vậy, bây giờ tôi hãy cho bạn một bản tóm tắt nhanh.

SUMIF (range, điều kiện, [sum_range])

• range – dải của các ô được đánh giá theo điều kiện mà bạn đưa ra, mang tính bắt buộc.
• criteria – điều kiện cần phải đáp ứng, mang tính bắt buộc
• sum_range – các ô tính tổng nếu thỏa điều kiện, mang tính tùy chọn.

Như bạn thấy, cú pháp của hàm Excel SUMIF chỉ cho phép một điều kiện. Tuy nhiên, bên trên, chúng tôi có nói rằng Excel SUMIF có thể được sử dụng để tính tổng các giá trị với nhiều điều kiện. Làm thế nào mà có thể được? Thực tế, bạn cần thêm các kết quả của vài hàm SUMIF và sử dụng các công thức SUMIF với các điều kiện mảng, như trong ví dụ tiếp theo.

Hàm SUMIFS – cú pháp và cách sử dụng:

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

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

SUMIFS (sum_range, criteria_range1, criteria1, [criter_range2, criteria2], ...)

3 đối số tiên phong là bắt buộc, những range được bổ trợ và những điều kiện kèm theo tương quan tới chúng thì được tùy chọn .

• sum_range – một hoặc nhiều ô tính tổng, mang tính bắt buộc. Đây 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 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.
• criteria_range1 – range đầu tiên được đánh giá theo các điều kiện liên quan, mang tính bắt buộc.
• criteria1 – điều kiện đầu tiên phải được đáp ứng, là đối số bắt buộc phải có. Bạn có thể cung cấp các điều kiện dưới dạng một số, biểu thức logic, tham chiếu ô, văn bản hoặc một hàm Excel khác. Ví dụ bạn có thể sử dụng các điều kiện như 10, “> = 10”, A1, “cherries” hoặc TODAY ().
• criteria_range2, criteria2, … – đây là các dải đượ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 có thể sử dụng tối đa 127 dải / điều kiện trong công thức SUMIFS.

Chú ý: Hàm SUMIFS trong excel 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 được chỉ định là đúng cho ô đó.

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

Khi bạn đang tiếp thu một cái mới mẻ và lạ mắt, bạn nên mở màn với những điều đơn thuần. Vì vậy, để mở màn, hãy xác lập tổng thể những đối số cho công thức SUMIFS của chúng tôi :

• sum_range – C2: C9
• criteria_range1 – A2: A9
• criteria1 – “apples”
• criteria_range2 – B2: B9
• criteria2 – “Pete”

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

= SUMIFS (C2: C9, A2: A9, "táo", B2: B9, "Pete")

Để khiến việc chỉnh sửa công thức đơn thuần hơn, bạn hoàn toàn có thể sửa chữa thay thế những tiêu chuẩn văn bản “ apples ” và “ Pete ” bằng những tham chiếu ô. Trong trường hợp này, bạn sẽ không phải biến hóa công thức để giám sát lượng trái cây khác từ một nhà phân phối khác nhau :

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

Xem thêm : Học Excel văn phòng
Mời những bạn theo dõi 2 videos sau đây và đừng quên tải về tài liệu kèm theo bên dưới để hiểu rõ hơn về hàm SUMIFS nhé !

Video nâng cao về 2 hàm SUMIF và SUMIFS sẽ giúp những bạn sử dụng những ký tự dấu sao và hỏi chấm để tính tổng nâng cao :

Sử dụng SUMIFS và SUMIF trong Excel – một số điều cần nhớ:

Vì mục tiêu của hướng dẫn này là gồm có tổng thể những cách hoàn toàn có thể có, để tính tổng những giá trị thỏa một số ít điều kiện kèm theo, tất cả chúng ta sẽ luận bàn những ví dụ công thức với cả hai hàm – SUMIFS và SUMIF với nhiều điều kiện kèm theo. Để sử dụng chúng một cách đúng mực, bạn cần phải hiểu rõ hai hàm này có điểm gì chung và phương pháp chúng khác nhau như thế nào .
Mặc dù nét chung thì khá rõ ràng – tương đương về điểm đến cho hiệu quả ở đầu cuối và những tham số – trong khi sự độc lạ dù không rõ rang nhưng vẫn rất thiết yếu .

1. Trình tự của các đối số

Trong những hàm Excel SUMIF và SUMIFS, thứ tự những đối số là khác nhau. Cụ thể, sum_range là tham số thứ nhất trong SUMIFS, nhưng lại đứng thứ 3 trong công thức SUMIF .
Khi mở màn học, bạn sẽ cảm thấy có vẻ như như Microsoft đã cố ý làm phức tạp hóa cho người học và người dùng nó. Tuy nhiên, khi xem xét kỹ hơn, bạn sẽ thấy nguyên do thực ra đằng sau nó. Vấn đề là sum_range là tùy chọn trong SUMIF. Nếu bạn bỏ lỡ nó, không có yếu tố, SUMIF công thức của bạn sẽ tính tổng những giá trị trong range ( tham số tiên phong ) .
Trong SUMIFS, sum_range là rất quan trọng và là bắt buộc, và đó là nguyên do tại sao nó đến trước. Có thể những người của Microsoft nghĩ rằng sau khi thêm những dải / điều kiện kèm theo xem xét thứ 10 hoặc 100, thì có ai đó hoàn toàn có thể quên xác lập dải để tính tổng 🙂
Tóm lại, nếu bạn đang sao chép và chỉnh sửa những hàm này, hãy bảo vệ bạn đặt những thông số kỹ thuật theo thứ tự đúng .

2. Kích thước của đối số sum_range và criteria_range

Trong hàm SUMIF, đối số sum_range không nhất thiết phải có cùng kích cỡ với đối số range, miễn là bạn có ô phía trên bên trái. Trong hàm SUMIFS, mỗi criteria_range phải chứa cùng 1 số ít hàng và cột như tham số sum_range .

Ví dụ, công thức = SUMIF (A2: A9, F1, C2: C18) sẽ trả lại kết quả đúng vì Excel chỉ xem ô phía trên bên trái trong đối số sum_range (C2 trong ví dụ này là đúng) và sau đó bao gồm số cột và hàng giống như kích thước của đối số range.

Công thức SUMIFS:  = SUMIFS (C2: C9, A2: A9, "apples", B2: B10, "Pete") sẽ báo lỗi # VALUE! vì criter_range2 (B2: B10) không khớp với criteria_range1 (A2: A9) và sum_range (C2: C9).

Có vẻ như tất cả chúng ta đã tiếp cận tạm xong triết lý rồi, nên mục sau tất cả chúng ta sẽ chuyển sang thao tác thực hành thực tế ( chính là những ví dụ công thức 🙂

Đăng ký ngay : Học Excel dành cho người đi làm

Làm thế nào để sử dụng SUMIFS trong Excel – ví dụ công thức

Vừa nãy, tất cả chúng ta đã luận bàn về một công thức SUMIFS đơn thuần với hai điều kiện kèm theo văn bản. Với chiêu thức tựa như như thế, bạn hoàn toàn có thể sử dụng Excel SUMIFS với nhiều điều kiện kèm theo bộc lộ bằng số, ngày, biểu thức logic, và những hàm Excel khác .

Ví dụ 1. Công thức SUMIFS với toán tử so sánh

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

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

 

Lưu ý : Hãy quan tâm rằng trong những công thức SUMIFS, những biểu thức lôgic với những toán tử so sánh phải luôn luôn được đặt trong dấu nháy kép ( “ ” ) .
Chúng tôi đã đề cập chi tiết cụ thể tổng thể những toán tử so sánh hoàn toàn có thể khi luận bàn về hàm Excel SUMIF, và những chúng cũng hoàn toàn có thể sử dụng trong điều kiện kèm theo SUMIFS. Ví dụ : Trả về giá trị tổng của toàn bộ những giá trị trong những ô C2 : C9 mà lớn hơn hoặc bằng 200 và nhỏ hơn hoặc bằng 300 .

= SUMIFS (C2: C9, C2: C9, “> = 200”, C2: C9, “<= 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 tổng những giá trị với nhiều điều kiện kèm theo dựa trên ngày hiện tại, hãy sử dụng hàm TODAY ( ) trong điều kiện kèm theo của hàm SUMIFS của bạn, như được trình diễn bên dưới. Công thức sau đâ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 chừng thời hạn 7 ngày vừa mới qua, có gồm có ngày thời điểm ngày hôm nay :

= SUMIFS (D2: D10, C2: C10, “> =” & TODAY () – 7, C2: C10, “<=” & TODAY ())

 

Chú thích. Khi bạn sử dụng một hàm Excel khác cùng với toán tử logic trong những điều kiện kèm theo, bạn phải sử dụng ký hiệu và ( và ) để nối với 1 chuỗi, ví dụ “ < = ” và 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 những giá trị trong một dải có tài liệu là ngày xác lập. Ví dụ : công thức SUMIFS sau sẽ thêm những giá trị trong những ô C2 : C9 nếu ngày trong cột B rơi trong khoảng chừng giữa ngày 1 tháng 10 năm năm trước và ngày 31 tháng 10 năm năm trước, như sau : = SUMIFS ( C2 : C9, B2 : B9, “ > = 10/1/2014 ”, B2 : B9, “ < = 10/31/2014 ” ) Kết quả tương tự như hoàn toàn có thể đạt được bằng cách đo lường và thống kê sự độc lạ của hai hàm SUMIF, như được minh họa trong ví dụ này – Cách sử dụng SUMIF để tính tổng những giá trị trong một dải ngày xác lập. Tuy nhiên, bạn cũng thấy rằng công thức SUMIFS là thuận tiện 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 nghiên cứu và phân tích báo cáo giải trình và tài liệu khác, bạn thường cần phải tính tổng những 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 – 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 các ô 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 các ô trắng “nhận định trực quan”, bao gồm các giá trị chứa 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 những giá trị trong những ô C2 : C10 ( có cùng những điều kiện kèm theo ) như công thức ở trên, nhưng có gồm có những 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 là các ô không trống, và có bao gồm chuỗi có chiều dài bằng 0
=SUMIFS(C2:C10, A2:A10, “<>”, B2:B10, “<>”)

Tính tổng giá trị trong các ô C2:C10 nếu các ô tương ứng với nó trong cột A và B không là ô trống, có 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 là các ô không trống, và không bao gồm chuỗi có chiều dài bằng 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 các ô C2:C10 nếu các ô tương ứng với nó trong cột A và B không là ô trống, và không bao gồm các ô với chuỗi trống.

Và giờ đây, 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 kèm theo “ trống ” và “ không trống ” trên tài liệu thực như thế nào :
Giả sử bạn 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 tính được tổng số mẫu sản phẩm chưa được giao ? Nghĩa là bạn muốn biết tổng những giá trị tương ứng với những ô không rỗng trong cột B và những ô rỗng trong cột C .
Giải pháp là sử dụng công thức SUMIFS với 2 điều kiện kèm theo :
= SUMIFS ( D2 : D10, B2 : B10, “ < > ”, C2 : C10, “ = ” )

Ví dụ 4: Cách viết điều kiện hàm SUMIFS

Đề bài: Cho bảng dữ liệu tại vùng F2:H10, tính các kết quả tại vùng B4:D7 theo các điều kiện tương ứng tại cột A (Mã) và dòng 3 (Ngày)
Bước 1: Phân tích đề bài

• Đề bài yêu cầu tính tổng theo 2 điều kiện là Ngày và Mã, do đó ta không thể sử dụng hàm SUMIF mà phải sử dụng hàm SUMIFS (ở đây chúng ta bỏ qua các hàm khác mà chỉ xét tính ứng dụng của hàm SUMIF/SUMIFS)
• Điều kiện cần tính không phải cố định mà tùy biến, viết 1 hàm SUMIFS tại B4 rồi copy hàm đó sang các ô khác để tính
• Điều kiện Mã và Ngày có thể thay đổi trong công thức.

Bước 2: Xây dựng công thức SUMIFS
Cấu trúc hàm SUMIFS(sum_range, Criteria_range1, Criteria1, …)

• Sum_range: là cột Số lượng trong vùng bảng dữ liệu F2:H10
• Criteria_range1: là cột Mã trong vùng bảng dữ liệu F2:H10
• Criteria1: là điều kiện về Mã (để tương ứng với Criteria_range1), với ô B4 là điều kiện tại ô A4
• Criteria_range2: là cột Ngày trong vùng bảng dữ liệu F2:H10
• Criteria2: là điều kiện về Ngày (tương ứng với Criteria_range2), với ô B4 là điều kiện tại ô B3

Do đó công thức tại ô B4 hoàn toàn có thể viết như sau :
B4 = SUMIFS ( H3 : H10, F3 : F10, A4, G3 : G10, B3 )

Xem thêm: Cách cố định dòng, cố định cột trong Excel
Bước 3: Tùy biến điều kiện trong hàm SUMIFS
Với việc copy sang bên phải (FillRight từ B4 tới D4): với thao tác này cần cố định điều kiện tại cột A, nên ô A4 sẽ cố định cột là $A4

Với việc copy xuống dưới ( FillDown từ B4 tới B7 ) : với thao tác này cần cố định và thắt chặt điều kiện kèm theo tại dòng 3, nên ô B3 sẽ cố định và thắt chặt cột là B USD 3
Vùng tài liệu F3 : H10 cũng cần cố định và thắt chặt để khi copy công thức thì vùng này không biến hóa
Do đó công thức tại B4 sẽ được thiết kế xây dựng lại thành :

B4 = SUMIFS ( USD H USD 3 : USD H USD 10, USD F USD 3 : USD F USD 10, USD A4, USD G USD 3 : USD G USD 10, B USD 3 )

Khi sao chép công thức sang những ô còn lại ta sẽ được tác dụng đúng như sau :

* Áp dụng:

Cách này thường dùng trong những nhu yếu về lập báo cáo giải trình cụ thể, báo cáo giải trình theo nhiều đối tượng người dùng cùng lúc .

Sử dụng hàm SUMIF có nhiều điều kiện OR

Như đã chú ý quan tâm ở phần đầu của bài này, thì hàm SUMIFS được phong cách thiết kế với AND. Nhưng nếu bạn cần tính tổng những giá trị có nhiều điều kiện kèm theo OR, nghĩa là khi có tối thiểu một trong những điều kiện kèm theo sẽ được cung ứng ?

Ví dụ 1. SUMIF + SUMIF

Giải pháp đơn thuần nhất là tính tổng những hiệu quả trả về bởi một số ít hàm SUMIF. Ví dụ : công thức sau đây sẽ minh họa cách tính tổng số mẫu sản phẩm do Mike và John phân phối :
= SUMIF ( C2 : C9, “ Mike ”, D2 : D9 ) + SUMIF ( C2 : C9, “ John ”, D2 : D9 )

Như bạn thấy, hàm SUMIF tiên phong cho biết số lượng tương ứng với “ Mike ”, còn hàm SUMIF kia thì trả lại số tiền tương quan đến “ John ” và sau đó bạn cộng hai tác dụng này lại .

Ví dụ 2. SUM & SUMIF với đối số mảng

Các giải pháp trên rất đơn thuần và thực sự hiệu suất cao cao khi chỉ có một vài điều kiện kèm theo. Nhưng một công thức SUMIF + SUMIF hoàn toàn có thể được tăng trưởng rất nhiều nếu bạn muốn tính tổng những giá trị với nhiều điều kiện kèm theo OR. Trong trường hợp này, cách tiếp cận tốt hơn là sử dụng một đối số như điều kiện kèm theo mảng trong hàm SUMIF, như sau :
Bạn hoàn toàn có thể khởi đầu bằng cách liệt kê tổng thể những điều kiện kèm theo của bạn, được ngăn cách bởi dấu phẩy và sau đó đặt chúng trong { dấu ngoặc nhọn }, điều này được gọi là mảng .
Trong ví dụ trước, nếu bạn muốn tính tổng những mẫu sản phẩm do John, Mike và Pete cung ứng, điều kiện kèm theo mảng của bạn sẽ như thế này { “ John ”, “ Mike ”, “ Pete ” }. Và hàm SUMIF hoàn hảo là : = SUMIF ( C2 : C9, { “ John ”, “ Mike ”, “ Pete ” }, D2 : D9 ) .
Đối số mảng gồm có 3 giá trị bắt buộc phải có trong công thức SUMIF để trả về ba tác dụng độc lập, nhưng vì tất cả chúng ta viết công thức trong một ô duy nhất, nó sẽ chỉ trả lại hiệu quả tiên phong – tức là tổng số mẫu sản phẩm do John phân phối. Để nó hoạt động giải trí, bạn phải sử dụng vài mẹo – lồng công thức SUMIF của bạn trong một hàm SUM, như sau :
= SUM ( SUMIF ( C2 : C9, { “ John ”, “ Mike ”, “ Pete ” }, D2 : D9 ) )

Như bạn thấy, một điều kiện kèm theo mảng như trên đã làm cho công thức nhỏ gọn hơn nhiều so với SUMIF + SUMIF, và chúng cho phép bạn thêm nhiều giá trị như bạn muốn trong mảng đó .
Phương pháp này sẽ chạy với những số lượng hoặc những giá trị văn bản. Ví dụ : nếu thay vì bạn có tên những nhà sản xuất trong cột C, mà bạn chỉ có ID nhà cung ứng như 1, 2, 3, v.v … thì công thức SUMIF của bạn sẽ như sau :
= SUM ( SUMIF ( C2 : C9, { 1,2,3 }, D2 : D9 ) )
Không giống như những giá trị văn bản, những số lượng không cần phải được gồm có trong dấu nháy kép trong đối số mảng .

Ví dụ 3. SUMPRODUCT & SUMIF

Trong trường hợp, Bạn thích liệt kê những điều kiện kèm theo trong vài ô thay vì chỉ định chúng trực tiếp trong công thức, thì bạn hoàn toàn có thể sử dụng SUMIF tích hợp với hàm SUMPRODUCT để nhân nhiều thành phần trong những mảng xác lập rồi trả lại tổng số lượng của những mẫu sản phẩm đó .

= SUMPRODUCT (SUMIF (C2: C9, G2: G4, D2: D9))

Trong trường hợp G2 : G4 là những ô chứa điều kiện kèm theo của bạn, thì itên nhà phân phối trong trường hợp này, được minh họa như trong ảnh chụp màn hình hiển thị bên dưới đây :
Nhưng tất yếu, không có gì ngăn cản nếu bạn muốn liệt kê những giá trị trong một điều kiện kèm theo mảng của hàm SUMIF ”

= SUMPRODUCT (SUMIF (C2: C9, {“Mike”, “John”, “Pete”}, D2: D9))

Kết quả trả về bởi cả hai công thức sẽ giống như những gì bạn thấy trong hình :

Excel SUMIFS có nhiều điều kiện OR

Nếu bạn muốn tính tổng có điều kiện kèm theo những giá trị trong Excel thì không riêng gì tương quan tới những điều kiện kèm theo OR, nhưng với 1 số ít điều kiện kèm theo, bạn sẽ phải sử dụng SUMIFS thay vì SUMIF. Các công thức sẽ như những gì mà tất cả chúng ta vừa đàm đạo .
Như thường lệ, một ví dụ hoàn toàn có thể giúp minh họa tốt hơn điều trên. Trong bảng những nhà sản xuất trái cây của chúng tôi, hãy thêm Delivery Date ( Ngày giao hàng ) ( cột E ) và tìm tổng số lượng được Mike, John và Pete giao vào tháng 10 .

Ví dụ 1. SUMIFS + SUMIFS

Công thức được tạo ra bởi cách chiêu thức này gồm có nhiều lần lặp đi lặp lại và trông có vẻ như rườm rà, tuy nhiên nhưng nó rất dễ hiểu, và điểm quan trọng nhất là nó hoạt động giải trí tốt 🙂

= SUMIFS (D2: D9, C2: C9, “Mike”, E2: E9, “> = 10/1/2014”, E2: E9, “<= 10/31/2014”) +

SUMIFS (D2: D9, C2: C9, “John”, E2: E9, “> = 10/1/2014”, E2: E9, “<= 10/31/2014”) +

SUMIFS (D2: D9, C2: C9, “Pete”, E2: E9, “> = 10/1/2014”, E2: E9, “<= 10/31/2014”)

Như bạn thấy, khi bạn viết một công thức SUMIFS riêng cho từng nhà cung ứng và thỏa cả hai điều kiện kèm theo – từ tháng 10 ( “ > = 10/1/2014 ” ) trở đi và từ 31 tháng 10 ( “ < = 10/31/2014 ” ) trở lại trước đó, và sau đó bạn tính tổng những hiệu quả .

Ví dụ 2. SUM & SUMIFS với đối số mảng

Tôi đã nỗ lực lý giải thực chất của cách tiếp cận này trong ví dụ với SUMIF, thế cho nên giờ đây tất cả chúng ta chỉ hoàn toàn có thể sao chép công thức đó, biến hóa thứ tự những đối số ( có sự khác nhau ở SUMIF và SUMIFS – như trên đã đề cập ) và thêm những điều kiện kèm theo bổ trợ. Công thức tác dụng sau sẽ nhỏ gọn hơn SUMIFS + SUMIFS :
= SUM ( SUMIFS ( D2 : D9, C2 : C9, { “ Mike ”, “ John ”, “ Pete ” }, E2 : E9, “ > = 10/1/2014 ”, E2 : E9, “ < = 10 / 31/2014 “ ) ) Kết quả trả về bằng công thức này giống như những gì bạn thấy trong ảnh chụp màn hình hiển thị ở trên .

Ví dụ 3. SUMPRODUCT & SUMIFS

Nếu như bạn nhớ, thì cách tiếp cận SUMPRODUCT khác với hai cách trước đó theo cách bạn nhập mỗi điều kiện kèm theo của bạn vào một ô riêng không liên quan gì đến nhau thay vì nhập chúng trực tiếp trong công thức. Trong trường hợp một số ít điều kiện kèm theo, hàm SUMPRODUCT sẽ không đủ và bạn sẽ phải sử dụng hàm ISNUMBER và hàm MATCH .
Vì vậy, giả sử rằng những tên người cung ứng nằm trong những ô H1 : H3, Ngày khởi đầu ( Start date ) nằm trong ô H4 và ngày kết thúc ( End date ) trong ô H5, công thức SUMPRODUCT của chúng tôi có dạng sau :
= SUMPRODUCT ( – ( E2 : E9 > = H4 ), – ( E2 : E9 < = H5 ), — ( ISNUMBER ( MATCH ( C2 : C9, H1 : H3, 0 ) ), D2 : D9 )

Sẽ có nhiều người tự hỏi rằng tại sao lại sử dụng 2 dấu gạch ngang (–) trong công thức SUMPRODUCT. Vấn đề đặt ra là hàm SUMPRODUCT bỏ qua tất cả các giá trị số, trong khi các toán tử so sánh trong công thức của chúng ta sẽ trả về các giá trị Boolean (TRUE / FALSE), không phải số. Để chuyển đổi các giá trị Boolean này thành 1 và 0, bạn sử dụng dấu hai chấm, được gọi là toán tử đơn vị. Các toán tử đơn vị đầu tiên được gán cho TRUE / FALSE như là -1/0, tương ứng. Toán tử đơn vị thứ hai sẽ phủ định giá trị, tức là đảo ngược dấu, biến chúng thành +1 và 0, và hàm SUMPRODUCT có thể hiểu được.

Tôi kỳ vọng lời lý giải trên có ý nghĩa với bạn. Và thậm chí còn nếu không, thì thực sự là bạn chỉ cần nhớ nguyên tắc này – sử dụng toán tử đôi ( – ) khi bạn sử dụng những toán tử so sánh trong những công thức SUMPRODUCT .

Sử dụng hàm SUM trong các công thức mảng

Như bạn đã biết, thì Microsoft đã triển khai hàm SUMIFS trong Excel 2007. Nếu ai đó vẫn sử dụng Excel 2003, 2000 hoặc phiên bản trước đó, thì bạn sẽ phải sử dụng công thức mảng SUM để thêm những giá trị có nhiều điều kiện kèm theo AND. Đương nhiên, chiêu thức tiếp cận này cũng hoạt động giải trí trong những phiên bản văn minh của Excel 2013 – 2007, và hoàn toàn có thể được coi là sự lỗi thời của hàm SUMIFS .
Tuy trong những công thức SUMIF đã bàn luận ở trên có sử dụng đối số mảng, nhưng một công thức mảng lại là một cái gì đó khác .

Ví dụ 1. Sum với các điều kiện AND trong Excel 2003 và phiên bản trước đó:

Chúng ta hãy quay lại ví dụ tiên phong mà tất cả chúng ta tính tổng số lượng ứng với một loại trái cây và nhà phân phối nhất định :

Như bạn đã biết, điều này hoàn toàn có thể thuận tiện triển khai với công thức SUMIFS thường thì :
= SUMIFS ( C2 : C9, A2 : A9, “ apples ”, B2 : B9, “ Pete ” )
Và giờ đây, tất cả chúng ta hãy cùng xem yếu tố tương tự như hoàn toàn có thể được thực thi như thế nào trong phiên bản Excel “ không có SUMIFS ”. Trước hết, bạn viết ra tổng thể những điều kiện kèm theo cần phải phân phối ở dạng dải = “ điều kiện kèm theo ”. Trong ví dụ này, tất cả chúng ta có hai cặp dải / điều kiện kèm theo :
Điều kiện 1 : A2 : A9 = “ apples ”
Điều kiện 2 : B2 : B9 = “ Pete ”
Sau đó, bạn viết một công thức SUM có nhân toàn bộ trong trường hợp : tương ứng với những cặp dải / điều kiện kèm theo của bạn, mỗi cặp được đặt trong ngoặc đơn. Hệ số nhân sau cuối là dải để tính tổng, là C2 : C9 trong trường hợp này :
= SUM ( ( A2 : A9 = “ apples ” ) * ( B2 : B9 = “ Pete ” ) * ( C2 : C9 ) )
Như được minh họa trong hình bên dưới, thì công thức này hoạt động giải trí rất tốt trong phiên bản Excel 2013 mới nhất .

Lưu ý: Khi nhập bất kỳ công thức mảng nào, bạn phải nhấn tổ hợp phím Ctrl + Shift + Enter. Chỉ như vậy thì công thức của bạn được đặt trong {dấu ngoặc nhọn} – một dấu hiệu cho biết một công thức mảng được nhập chính xác. Nếu bạn cố gõ dấu ngoặc bằng tay, thì công thức của bạn sẽ được chuyển đổi thành một chuỗi văn bản và nó sẽ không hoạt động.

Ví dụ 2. Các công thức mảng SUM trong các phiên bản Excel hiện đại

Ngay cả trong những phiên bản tân tiến của Excel 2013, 2010 hoặc 2007, sức mạnh của hàm SUM không khi nào bị nhìn nhận thấp. Công thức mảng SUM không chỉ đơn thuần là rèn luyện hướng tâm lý mà còn có giá trị thực tiễn, như trong ví dụ sau đây :
Giả sử bạn có hai cột, B và C, và bạn cần phải biết được cột C lớn hơn gấp bao nhiêu lần so với cột B, khi một giá trị trong cột C lớn hơn hoặc bằng 10. Một giải pháp mà bạn hoàn toàn có thể nghĩ ra đo là sử dụng công thức mảng SUM :

= SUM ((C1: C10> = 10) * (C1: C10> B1: B10))

 

Nếu bạn vẫn không thấy bất kể ứng dụng thực tiễn nào so với công thức trên ? Hãy tâm lý về nó theo một cách khác 🙂
Giả sử bạn có list đơn đặt hàng như được hiển thị trong ảnh chụp màn hình hiển thị dưới đây và bạn muốn biết có bao nhiêu mẫu sản phẩm chưa được gửi đầy hết trong một ngày xác lập. Chúng tôi có những điều kiện kèm theo sau ( được dịch sang ngôn từ của Excel ) :

• Điều kiện 1: Giá trị trong cột B (Ordered items) lớn hơn 0
• Điều kiện 2: Một giá trị trong cột C (Delivered) ít hơn trong cột B
• Điều kiện 3: Ngày trong cột D (Due date) ít hơn 11/1/2014.

Đặt ba cặp range / điều kiện kèm theo lại với nhau, bạn sẽ nhận được công thức sau :

= SUM ((B2: B10> = 0) * (B2: B10> C2: C10) * (D2: D10

 

Các ví dụ công thức thảo luận trong bài này có chỉ là một phần của tảng băng trôi của hàm SUMIFS và SUMIF. Nhưng hy vọng rằng, chúng đã chỉ ra hướng suy nghĩ cho bạn để hiện tại bạn có thể tính tổng các giá trị trong bảng tính Excel (không quan tâm đến rằng có bao nhiêu điều kiện phức tạp mà cần bạn cân nhắc)

Thông tin bổ ích khác trong việc ứng dụng hàm Sumifs

• Với hàm SUMIFS, bạn có thể thêm đến 127 điều kiện hay tiêu chí một lần. Tuy nhiên,càng nhiều tiêu chí càng dẫn đến sai sót, vì vậy hãy cố gắng viết ngắn gọn.
• Nếu có bất kì ô trống nào trong các dãy tiêu chí (criteria range), Excel sẽ cho kết quả trả về là “không thực hiện được”. Vì vậy, hãy kiểm tra và điền đầy đủ vào các dãy, đặc biệt criteria range để được kết quả chính xác.
• Chúng ta có thể sử dụng các kí tự đặc biệt trong công thức SUMIFS. Ví dụ: “?” là tìm kiếm một chữ cái và “*” là phần còn lại sau các chữ cái cụ thể nào đó.

Ngoài hàm SumIf, SumIfS giúp tính tổng theo nhiều điều kiện kèm theo, tất cả chúng ta còn hoàn toàn có thể sử dụng những hàm khác như SUMPRODUCT để thao tác này. Trong trong thực tiễn thì nhu yếu thống kê giám sát và đặc thù tài liệu sẽ quyết định hành động tới việc tất cả chúng ta sẽ cần dùng hàm nào, dùng như thế nào. Do đó bạn cần bổ trợ thêm cho mình càng nhiều kỹ năng và kiến thức về hàm, về tổ chức triển khai tài liệu thì càng thuận tiện khi thao tác .

Xem thêm: Cách dùng hàm SUMIF trong Excel 2021 – công thức ví dụ Dễ Hiểu

Tiếp đó là việc cần phải kết hợp các công cụ của Excel để tăng hiệu quả công việc như: định dạng dữ liệu, định dạng theo điều kiện, trích lọc, sắp xếp, báo cáo bằng Pivot Table, vẽ biểu đồ…Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học Excel từ cơ bản đến chuyên gia dành cho người đi làm

Source: http://wp.ftn61.com
Category: Hỏi Đáp