HÀM TÍNH TỔNG NHIỀU ĐIỀU KIỆN

Trong nội dung 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ới SUMIFS vào excel theo cú pháp và cách sử dụng của chúng, ở kề bên đó, cũng cung cấp một số lấy một ví dụ về phương pháp để tính tổng các giá trị có rất 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ới trước nữa.

Bạn đang xem: Hàm tính tổng nhiều điều kiện

Sử dụng SUMIFS với SUMIF trong Excel – một vài điều bắt buộc nhớ:Làm thế nào để sử dụng SUMIFS vào Excel – ví dụ như công thứcSử dụng hàm SUMIF có tương đối nhiều điều khiếu nại ORExcel SUMIFS có rất nhiều điều khiếu nại ORSử dụng hàm SUM trong số công thức mảng

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

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

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

range – dải của các ô được reviews theo đk mà bạn đưa ra, mang ý nghĩa bắt buộc.criteria – điều kiện rất cần được đáp ứng, mang tính chất bắt buộcsum_range – những ô tính tổng nếu như thỏa điều kiện, mang ý nghĩa tùy chọn.

Như các bạn thấy, cú pháp của hàm Excel SUMIF chỉ cho phép một điều kiện. Mặc dù nhiên, bên trên, shop 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 khá nhiều điều kiện. Làm nắm nào mà hoàn toàn có thể được? Thực tế, bạn phải thêm các hiệu quả của vài hàm SUMIF cùng sử dụng các công thức SUMIF với những điều kiện mảng, như trong lấy ví dụ như tiếp theo.

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

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

So cùng với SUMIF, thì cú pháp SUMIFS tinh vi hơn một chút:

SUMIFS (sum_range, criteria_range1, criteria1, , ...)

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

sum_range – một hoặc những ô tính tổng, mang tính chất bắt buộc. Đây rất có thể là một ô duy nhất, một dải ô hoặc dải tất cả tên. Chỉ có các ô chỉ chứa số mới được xem tổng; Còn quý hiếm ô trống và giá trị văn bạn dạng thì sẽ ảnh hưởng bỏ qua.criteria_range1 – range thứ nhất được đánh giá theo những điều khiếu nại liên quan, mang tính chất bắt buộc.criteria1 – điều kiện trước tiên phải được đáp ứng, là đối số bắt buộc phải có. Bạn cũng có thể cung cấp những điều kiện bên dưới dạng một số, biểu thức logic, tham chiếu ô, văn phiê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 khiếu nại như 10, “> = 10”, A1, “cherries” hoặc TODAY ().criteria_range2, criteria2, … – đấy là các dải được thêm vào và đk liên quan liêu tới các dải này, do các bạn tùy chọn. Bạn cũng có thể sử dụng tối đa 127 dải / đk trong bí quyết SUMIFS.

Chú ý: Hàm SUMIFS trong excel chuyển động với biểu thức xúc tích và ngắn gọn với AND, nghĩa là từng ô vào đối số sum_range chỉ được xem tổng nếu toàn bộ các đk được hướng đẫn là chuẩn cho ô đó.

Và bây giờ, bọn họ hãy coi hàm SUMIFS hoạt động với nhì điều kiện. đưa sử chúng ta có một bảng liệt kê các lô mặt hàng trái cây từ những nhà cung cấp khác nhau. Bạn có tên quả trong cột A, tên ở trong nhà cung cung cấp trong cột B và con số trong cột C. Bạn có nhu cầu tìm ra số tiền tương quan đến quả và nhà cung cấp, ví dụ: toàn bộ táo (apples) được hỗ trợ bởi Pete.

Khi ai đang tiếp thu một chiếc mới mẻ, bạn nên ban đầu với số đông điều solo giản. Bởi vì vậy, để bắt đầu, hãy xác minh tất cả các đối số cho cách làm SUMIFS của chúng tôi:

sum_range – C2: C9criteria_range1 – A2: A9criteria1 – “apples”criteria_range2 – B2: B9criteria2 – “Pete”

Bây tiếng tập hòa hợp các thông số trên, và các 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 giản dễ dàng hơn, bạn cũng có thể thay thế những tiêu chuẩn văn bản “apples” với “Pete” bằng những tham chiếu ô. Vào trường thích hợp này, các bạn sẽ không phải chuyển đổi công thức để đo lường và tính toán lượng hoa trái khác từ một nhà cung ứng khác nhau:

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

Mời các bạn theo dõi 2 videos tiếp sau đây và nhớ là download tư liệu kèm theo bên dưới để nắm rõ hơn về hàm SUMIFS nhé!

Video nâng cao về 2 hàm SUMIF và SUMIFS vẫn giúp chúng ta sử dụng những ký tự vết sao cùng hỏi chấm nhằm tính tổng nâng cao:

Sử dụng SUMIFS với SUMIF vào Excel – một số điều đề nghị nhớ:

Vì mục đích của chỉ dẫn này là bao gồm tất cả các cách hoàn toàn có thể có, để tính tổng những giá trị thỏa một trong những điều kiện, bọn họ sẽ bàn thảo các ví dụ như công thức với tất cả hai hàm – SUMIFS cùng SUMIF với nhiều điều kiện. Để thực hiện chúng một cách chủ yếu xác, bạn phải hiểu rõ nhì hàm này còn có điểm gì thông thường và phương pháp chúng khác nhau như thay nào.

Mặc cho dù nét bình thường thì khá rõ ràng – tương đồng về điểm đến cho hiệu quả cuối thuộc và các tham số – trong những lúc sự khác biệt dù không rõ rang nhưng mà vẫn rất đề nghị thiết.

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

Trong các hàm Excel SUMIF với SUMIFS, sản phẩm tự các đối số là khác nhau. Ráng thể, sum_range là tham số đầu tiên trong SUMIFS, tuy nhiên lại đứng số 3 trong công thức SUMIF.

Xem thêm: Iphone 6S 32Gb Xách Tay Hàn Quốc Cũ 99%, Iphone 6S 32Gb Công Ty Mới 100%

Khi bước đầu học, các bạn sẽ cảm thấy có vẻ như Microsoft đã nạm ý làm phức hợp hóa cho tất cả những người học và người tiêu dùng nó. Tuy nhiên, lúc xem xét kỹ hơn, bạn sẽ thấy nguyên nhân thực chất phía sau nó. Sự việc là sum_range là tùy chọn trong SUMIF. Nếu khách hàng bỏ qua nó, không có vấn đề, SUMIF công thức của các bạn sẽ tính tổng các giá trị trong range (tham số đầu tiên).

Trong SUMIFS, sum_range là rất đặc trưng và là bắt buộc, và đó là vì sao tại sao nó mang đến trước. Rất có thể những tín đồ của Microsoft nghĩ rằng sau khi thêm những dải/điều kiện chăm chú thứ 10 hoặc 100, thì gồm ai đó hoàn toàn có thể quên xác định dải để tính tổng:)

Tóm lại, nếu như khách hàng đang coppy và chỉnh sửa những hàm này, hãy bảo vệ bạn để các thông số theo thiết bị tự đúng.

2. Size của đối số sum_range và criteria_range

Trong hàm SUMIF, đối số sum_range không duy nhất thiết phải có cùng size với đối số range, miễn là chúng ta có ô bên trên bên trái. Vào hàm SUMIFS, từng criteria_range yêu cầu chứa cùng một số hàng và cột như thông số sum_range.

Ví dụ, cách làm = SUMIF (A2: A9, F1, C2: C18) sẽ trả lại hiệu quả đúng bởi Excel chỉ coi ô phía bên trên bên trái trong đối số sum_range (C2 trong ví dụ như này là đúng) với sau đó bao gồm số cột cùng hàng y hệt 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! bởi criter_range2 (B2: B10) không khớp với criteria_range1 (A2: A9) và sum_range (C2: C9).

Có vẻ như bọn họ đã tiếp cận tạm xong xuôi lý thuyết rồi, yêu cầu mục sau bọn họ sẽ chuyển sang thao tác làm việc thực hành (chính là các ví dụ công thức:)

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

Làm ráng nào để áp dụng SUMIFS trong Excel – ví dụ công thức

Vừa nãy, chúng ta đã đàm đạo về một cách làm SUMIFS dễ dàng với hai điều kiện văn bản. Với cách thức tương tự như thế, chúng ta có thể sử dụng Excel SUMIFS với rất nhiều điều kiện thể hiện bằng số, ngày, biểu thức logic, và những hàm Excel khác.

Ví dụ 1. Bí quyết SUMIFS với toán tử so sánh

Trong bảng hỗ trợ trái cây dưới đây, giả sử, bạn muốn tính tổng tất cả các lượng hàng đã làm được Mike cung cấp 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 các điều khiếu nại 2 với nhận được phương pháp SUMIFS sau:

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

 

Lưu ý: Hãy lưu ý rằng trong các công thức SUMIFS, các biểu thức lôgic với những toán tử so sánh phải luôn luôn luôn được đặt trong vết nháy kép (“”).

Chúng tôi sẽ đề cập cụ thể tất cả những toán tử so sánh có thể khi luận bàn về hàm Excel SUMIF, và các chúng cũng rất có thể sử dụng trong điều kiện SUMIFS. Ví dụ: Trả về quý hiếm tổng của tất cả các giá chỉ trị trong số ô C2: C9 mà to hơn hoặc bằng 200 và nhỏ hơn hoặc bằng 300.

= SUMIFS (C2: C9, C2: C9, “> = 200”, C2: C9, “Ví dụ 2. Sử dụng công thức SUMIFS cùng với ngày

Trong ngôi trường hợp bạn có nhu cầu tính tổng những giá trị với khá nhiều điều kiện dựa trên ngày hiện tại tại, hãy thực hiện hàm TODAY () trong đk 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 mức trong cột D nếu ngày khớp ứng trong cột C rơi vào hoàn cảnh khoảng thời hạn 7 ngày vừa qua, có bao gồm ngày hôm nay:

Khi phân tích report và tài liệu khác, các bạn thường rất cần được tính tổng các giá trị tương ứng với ô trống hoặc ko trống.

	Điều kiện	Mô tả	Công thưc ví dụ
Những ô trống	“=”	Tính tổng những giá trị mà gồm ô trống tương xứng (hoàn toàn không cất dũ liệu – ko công thức, cùng chuỗi gồm 0 kí tự)	=SUMIFS(C2:C10, A2:A10, “=”, B2:B10, “=”) Tính tổng giá trị trong số ô C2:C10 nếu các ô tương xứng với nó trong cột A và B là ô trọn vẹn trống.
“”	Tính tổng các giá trị tương ứng với các ô white “nhận định trực quan”, bao gồm các quý hiếm chứa những chuỗi rỗng được trả về bởi một trong những hàm Excel khác (ví dụ: ô gồm công thức như = “”).	=SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”) Tính tổng các giá trị trong số ô C2:C10 (có cùng những điều kiện) như bí quyết ở trên, dẫu vậy có bao hàm các chuỗi trống.
Những ô không trống	“”	Tính tổng những giá trị nhưng mà có các giá trị khớp ứng là các ô không trống, với có bao gồm chuỗi bao gồm chiều dài bởi 0	=SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”) Tính tổng giá trị trong những ô C2:C10 nếu các ô tương ứng với nó trong cột A cùng B không là ô trống, có bao gồm các ô với chuỗi trống.
SUM-SUMIFhaySUM / LEN	Tính tổng các giá trị cơ mà có các giá trị khớp ứng là những ô ko trống, và không bao gồm chuỗi tất cả chiều dài bở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á bán trị trong các ô C2:C10 nếu các ô khớp ứng với nó trong cột A với B không là ô trống, với không bao hàm các ô với chuỗi trống.

Và bây giờ, hãy thuộc xem cách bạn cũng có thể sử dụng cách làm SUMIFS với đk “trống” với “không trống” trên dữ 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 cầm nào để bạn tính được tổng số sản phẩm chưa được giao? Nghĩa là bạn có nhu cầu biết tổng các giá trị tương xứng với các ô không rỗng vào cột B và những ô trống rỗng trong cột C.

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

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

Ví dụ 4: phương pháp viết điều kiện hàm SUMIFS

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

Đề bài xích 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à lại phải sử dụng hàm SUMIFS (ở đây họ bỏ qua những hàm khác cơ mà chỉ xét tính ứng dụng của hàm SUMIF/SUMIFS)Điều kiện phải tính chưa hẳn cố định mà lại tùy biến, viết 1 hàm SUMIFS tại B4 rồi copy hàm đó thanh lịch các ô khác để tínhĐiều kiện Mã và Ngày rất có thể thay đổi trong công thức.

Bước 2: Xây dựng phương pháp SUMIFSCấu trúc hàm SUMIFS(sum_range, Criteria_range1, Criteria1, …)

Sum_range: là cột con số trong vùng bảng tài liệu F2:H10Criteria_range1: là cột Mã trong vùng bảng tài liệu F2:H10Criteria1: là điều khiếu nại về Mã (để tương ứng với Criteria_range1), với ô B4 là điều kiện tại ô A4Criteria_range2: là cột Ngày trong vùng bảng dữ liệu F2:H10Criteria2: là điều kiện về Ngày (tương ứng cùng với Criteria_range2), với ô B4 là điều khiếu nại tại ô B3

Do đó cách làm tại ô B4 rất có thể viết như sau: