Trong bài viết này, học Excel Online sẽ phân tích và lý giải sự khác biệt giữa các hàm SUMIF cùng SUMIFS trong excel theo cú pháp với cách thực hiện của chúng, ở bên cạnh đó, cũng hỗ trợ một số lấy một ví dụ về cách làm để tính tổng những giá trị có tương đối nhiều điều khiếu nại đồ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.

Bạn đang xem: Dùng hàm sumif nhiều điều kiện


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

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

Hàm SUMIF được thực hiện để tính tổng tất cả điều kiện, dựa trên một điều kiện. Shop chúng tôi đã trao đổi về cú pháp của chính nó một cách chi tiết trong nội dung bài viết trước, vày vậy, hiện nay tôi hãy cho chính 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 review theo điều kiện mà bạn đưa ra, mang tính chất bắt buộc.criteria – điều kiện cần phải đáp ứng, mang ý nghĩa bắt buộcsum_range – những ô tính tổng nếu như thỏa điều kiện, mang tính chất tùy chọn.

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

Hàm SUMIFS – cú pháp và bí quyết sử dụng:

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

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

Chú ý: Hàm SUMIFS vào excel hoạt động với biểu thức lô ghích với AND, nghĩa là mỗi ô trong đối số sum_range chỉ được xem tổng nếu toàn bộ các điều kiện được chỉ định và hướng dẫn là chuẩn cho ô đó.

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

*

Khi nhiều người đang tiếp thu một cái mới mẻ, các bạn nên bước đầu với đông đảo điều đối chọi giản. Vì chưng vậy, để bắt đầu, hãy khẳng định tất cả các đối số cho phương pháp SUMIFS của bọn chúng tôi:

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

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

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

*

Để khiến việc sửa đổi công thức đơn giản dễ dàng hơn, chúng ta cũng có thể thay thế những tiêu chuẩn văn phiên bản “apples” với “Pete” bằng các tham chiếu ô. Vào trường đúng theo này, các bạn sẽ không phải chuyển đổi công thức để thống kê giám sát lượng hoa quả khác từ một nhà hỗ trợ khác nhau:

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

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

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


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

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

Mặc dù nét phổ biến thì khá rõ ràng – tương đương về điểm đến lựa chọn cho kết quả cuối cùng và các tham số – trong những lúc sự biệt lập dù ko rõ rang nhưng lại vẫn rất đề xuất thiết.

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

Trong những hàm Excel SUMIF cùng SUMIFS, thứ tự những đối số là khác nhau. Vắt thể, sum_range là tham số đầu tiên trong SUMIFS, dẫu vậy lại đứng số 3 trong công thức SUMIF.

Xem thêm: Samsung S7 Edge Màu Hồng Vàng Chính Thức Ra Mắt Tại Việt Nam

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

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

Tóm lại, nếu bạn đang xào nấu và chỉnh sửa những hàm này, hãy bảo đảm an toàn bạn đặt các thông số theo vật dụng tự đúng.

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

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

Ví dụ, phương pháp = SUMIF (A2: A9, F1, C2: C18) sẽ trả lại kết quả đúng bởi vì Excel chỉ coi ô phía trên bên trái trong đối số sum_range (C2 trong ví dụ này là đúng) và sau đó bao hàm số cột và hàng 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) sum_range (C2: C9).

Có vẻ như họ đã tiếp cận tạm kết thúc lý thuyết rồi, nên mục sau chúng ta sẽ chuyển sang làm việc thực hành (chính là những 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 nỗ lực nào để áp dụng SUMIFS trong Excel – lấy ví dụ như công thức

Vừa nãy, họ đã trao đổi về một bí quyết SUMIFS dễ dàng với hai đk văn bản. Với cách thức tương tự như thế, bạn cũng có thể sử dụng Excel SUMIFS với nhiều điều khiếu nại thể hiện bởi số, ngày, biểu thức logic, và các hàm Excel khác.

Ví dụ 1. Phương pháp SUMIFS với toán tử so sánh

Trong bảng cung cấp trái cây dưới đây, đưa sử, bạn muốn tính tổng tất cả các lượng hàng đã có được Mike cung cấp với số lượng. Trường đoản cú 200 trở lên. Để có tác dụng điều này, bạn áp 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à nhận được bí quyết SUMIFS sau:

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

 

*

Lưu ý: Hãy chú ý 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 luôn được đặt trong lốt nháy kép (“”).

Chúng tôi đang đề cập cụ thể tất cả các toán tử so sánh rất có thể khi luận bàn về hàm Excel SUMIF, và những chúng cũng rất có thể sử dụng trong đk SUMIFS. Ví dụ: Trả về quý giá tổng của tất cả các giá chỉ trị trong các ô 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. áp dụng công thức SUMIFS cùng với ngày

Trong ngôi trường hợp bạn muốn tính tổng những giá trị với tương đối nhiều điều kiện dựa vào ngày hiện nay tại, hãy thực hiện 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 mức trong cột D trường hợp ngày tương xứng trong cột C lâm vào hoàn cảnh khoảng thời gian 7 ngày vừa qua, có bao hàm ngày hôm nay:

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

Điều kiệnMô 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 cất 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 số ô C2:C10 nếu các ô tương xứng với nó trong cột A với B là ô hoàn toàn trống.

“”Tính tổng các giá trị khớp ứng với các ô white “nhận định trực quan”, bao hàm các quý hiếm chứa những chuỗi trống rỗng được trả về bởi một trong những hàm Excel khác (ví dụ: ô bao gồm công thức như = “”).=SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)

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

Những ô ko trống“”Tính tổng các giá trị cơ mà có những giá trị khớp ứng là những ô không trống, và có bao hàm chuỗi gồm 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 ô khớp ứng với nó trong cột A cùng B không là ô trống, có bao hàm các ô cùng với chuỗi trống.

SUM-SUMIFhaySUM / LENTính tổng những giá trị nhưng mà có những giá trị tương ứng là các ô không trống, và không bao hàm chuỗi bao gồm 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á trị trong số ô C2:C10 nếu các ô tương ứng với nó trong cột A cùng B ko là ô trống, và không bao gồm các ô cùng 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ông thức SUMIFS với điều kiện “trống” và “không trống” trên tài liệu thực như thế nào:

Giả sử các bạn có ngày đặt hàng trong cột B, ngày ship hàng trong cột C và con số trong cột D. Làm ráng 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 ứng với các ô không rỗng vào cột B và những ô rỗng trong cột C.

Giải pháp là thực hiện công thức SUMIFS với 2 điều kiện:

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

*

Ví dụ 4: giải 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 tác dụng tại vùng B4:D7 theo các điều kiện tương ứng trên cột A (Mã) và loại 3 (Ngày)Bước 1: Phân tích đề bài

Đề bài yêu ước tính tổng theo 2 điều kiện là Ngày với Mã, do đó ta không thể thực hiện hàm SUMIF nhưng phải thực hiện hàm SUMIFS (ở đây họ bỏ qua các hàm khác nhưng chỉ xét tính ứng dụng của hàm SUMIF/SUMIFS)Điều kiện nên tính chưa phải cố định nhưng 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ã cùng Ngày rất có thể thay đổi vào công thức.

Bước 2: Xây dựng cách làm 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 cùng với Criteria_range1), với ô B4 là điều khiếu nại tại ô A4Criteria_range2: là cột Ngày vào vùng bảng dữ liệu F2:H10Criteria2: là điều khiếu nại về Ngày (tương ứng cùng với Criteria_range2), với ô B4 là điều kiện tại ô B3

Do đó công thức tại ô B4 rất có thể viết như sau: