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à SUMIFS vào excel theo cú pháp với cách thực hiện của chúng, lân cận đó, cũng cung cấp một số ví dụ như 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 toàn bộ không đồng thời xảy ra (OR) vào phiên bạn dạng Excel 2013, 2010, 2007, 2003 và trước nữa.

Bạn đang xem: Cách dùng hàm sumifs trong excel 2010


Sử dụng SUMIFS và SUMIF vào Excel – một trong những điều đề nghị nhớ:Làm thế nào để thực hiện SUMIFS trong Excel – 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ó không ít điều khiếu nại ORSử dụng hàm SUM trong các công thức mảng

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

Hàm SUMIF được áp dụng để tính tổng bao gồm điều kiện, dựa vào một điều kiện. Cửa hàng chúng tôi đã bàn bạc về cú pháp của nó một cách chi tiết trong bài viết trước, bởi vậy, hiện giờ 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à chúng ta đưa ra, mang tính bắt buộc.criteria – điều kiện rất cần phải đáp ứng, mang ý nghĩa bắt buộcsum_range – các ô tính tổng ví như thỏa điều kiện, mang tính chất tùy chọn.

Như chúng ta 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. Tuy nhiên, bên trên, shop chúng tôi có bảo rằng Excel SUMIF rất có thể được áp dụng để tính tổng những giá trị với nhiều điều kiện. Làm cố 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 ba hàm SUMIF cùng sử dụng các công thức SUMIF với các điều kiện mảng, như trong lấy ví dụ tiếp theo.

Hàm SUMIFS – cú pháp và bí quyết 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 vào excel sẽ được reviews trong Excel 2007, vì chưng vậy chúng ta cũng có thể sử dụng nó trong toàn bộ các phiên bạn dạng của Excel 2013, 2010 với 2007.

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

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

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


*

*

sum_range – một hoặc nhiều ô tính tổng, mang ý nghĩa bắt buộc. Đây rất 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 tính tổng; Còn giá trị ô trống và quý giá văn bạn dạng thì sẽ bị bỏ qua.criteria_range1 – range đầu tiên được reviews theo những điều khiếu nại liên quan, mang tính bắt buộc.criteria1 – điều kiện trước tiên phải được đáp ứng, là đối số cần phải có. Chúng ta 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 phiên bản hoặc một hàm Excel khác. Ví dụ chúng ta cũng 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 sản xuất và đk liên quan tiền tới những dải này, do chúng ta tùy chọn. Chúng ta có thể sử dụng tối đa 127 dải / điều kiện trong cách làm SUMIFS.

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

Và bây giờ, họ hãy coi hàm SUMIFS vận động với nhì điều kiện. Mang sử chúng ta có một bảng liệt kê các lô sản phẩm trái cây từ những 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à số lượng trong cột C. Bạn muốn tìm ra số tiền tương quan đến quả cùng 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 ban đầu với phần đa điều 1-1 giản. Vì chưng vậy, để bắt đầu, hãy xác định tất cả những đối số cho công thức 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 thích hợp các thông số kỹ thuật 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 cho việc chỉnh sửa công thức đơn giản và dễ dàng hơn, bạn cũng có thể thay thế những tiêu chuẩn chỉnh văn bạn dạng “apples” với “Pete” bằng những tham chiếu ô. Trong trường vừa lòng này, bạn sẽ không phải biế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à 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à nhớ rằng download tài 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ới SUMIFS đang giúp chúng ta sử dụng các ký tự vệt sao và hỏi chấm nhằm tính tổng nâng cao:


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

Vì mục tiêu 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ó, nhằm tính tổng những giá trị thỏa một vài điều kiện, bọn họ sẽ bàn thảo các ví dụ như công thức đối với cả hai hàm – SUMIFS với SUMIF với khá nhiều điều kiện. Để áp dụng chúng một cách chủ yếu xác, bạn cần phải hiểu rõ hai hàm này có điểm gì chung và phương thức chúng khác nhau như gắng nào.

Mặc mặc dù nét phổ biến thì khá cụ thể – tương đương về điểm đến cho hiệu quả cuối cùng và những tham số – trong khi sự khác hoàn toàn dù không rõ rang nhưng mà vẫn rất buộc phải thiết.

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

Trong những hàm Excel SUMIF với SUMIFS, lắp thêm tự những đối số là không giống nhau. Núm thể, sum_range là tham số thứ nhất trong SUMIFS, nhưng mà lại đứng thứ 3 trong công thức SUMIF.

Xem thêm: Bao Đời Sống Và Hôn Nhân Đời Sống, Đời Sống Hôn Nhân

Khi bước đầu học, các bạn sẽ cảm thấy có vẻ như như Microsoft đã cố kỉnh ý làm phức tạp hóa cho tất cả những người học và người dùng nó. Mặc dù nhiên, khi chứng kiến tận mắt xét kỹ hơn, bạn sẽ thấy tại sao thực chất ẩn dưới nó. Vụ việc là sum_range là tùy chọn trong SUMIF. Nếu khách hàng bỏ qua nó, không tồn tại vấn đề, SUMIF phương pháp 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 và là bắt buộc, và đó là tại sao tại sao nó đến trước. Hoàn toàn có thể những bạn của Microsoft nghĩ rằng sau khoản thời gian thêm những dải/điều kiện để ý thứ 10 hoặc 100, thì có ai đó hoàn toàn có thể quên xác định dải nhằm tính tổng:)

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

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

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

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

Có vẻ như chúng ta đã tiếp cận tạm hoàn thành lý thuyết rồi, nên mục sau bọn họ sẽ gửi sang thao tác thực hành (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 nuốm nào để sử dụng SUMIFS trong Excel – ví dụ công thức

Vừa nãy, chúng ta đã luận bàn về một cách làm SUMIFS đơn giản dễ dàng với hai điều kiện văn bản. Với phương pháp tương từ bỏ như thế, bạn có thể sử dụng Excel SUMIFS với khá nhiều điều kiện 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 hỗ trợ trái cây bên dưới đây, mang sử, bạn muốn tính tổng toàn bộ các lượng hàng đã làm được Mike cung ứng với số lượng. Từ bỏ 200 trở lên. Để làm điều này, bạn thực hiện toán tử đối chiếu “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 những công thức SUMIFS, những biểu thức lôgic với các toán tử so sánh phải luôn luôn được đặt trong vế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 bàn thảo về hàm Excel SUMIF, và các chúng cũng hoàn toàn có thể sử dụng trong đk SUMIFS. Ví dụ: Trả về cực hiếm tổng của tất cả các giá trị trong những ô C2: C9 mà to hơn hoặc bởi 200 và bé dại hơn hoặc bởi 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 trường hợp bạn muốn tính tổng những giá trị với nhiều điều kiện dựa trên ngày hiện tại, hãy áp dụng hàm TODAY () trong điều kiện của hàm SUMIFS của bạn, như được trình diễn bên dưới. Công thức tiếp sau đây tính tổng giá trị trong cột D nếu ngày khớp ứng trong cột C rơi vào cảnh khoảng thời hạn 7 ngày vừa qua, có bao hàm ngày hôm nay:

Khi phân tích report và dữ liệu khác, bạn thường rất cần được tính tổng những giá trị tương ứ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 những giá trị mà có ô trống tương xứng (hoàn toàn không cất dũ liệu – không công thức, với chuỗi tất cả 0 kí tự)=SUMIFS(C2:C10, A2:A10, “=”, B2:B10, “=”)

Tính tổng giá chỉ trị trong những ô C2:C10 nếu các ô khớp ứng với nó vào cột A cùng B là ô hoàn toàn trống.

“”Tính tổng các giá trị khớp ứng với những ô trắng “nhận định trực quan”, bao hàm các quý giá chứa các chuỗi trống rỗng được trả về bởi một vài 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 những điều kiện) như công thức ở trên, nhưng mà có bao gồm các chuỗi trống.

Những ô ko trống“”Tính tổng những giá trị mà lại có những giá trị khớp ứng là các ô ko trống, cùng có bao gồm chuỗi có chiều dài bởi 0=SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)

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

SUM-SUMIFhaySUM / LENTính tổng các giá trị nhưng có những giá trị tương ứng là các ô ko trống, và không bao gồm chuỗi gồm 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á chỉ trị trong các ô C2:C10 nếu những ô khớp ứng với nó vào cột A với B không là ô trống, với không bao gồm các ô với chuỗi trống.

Và bây giờ, hãy thuộc xem cách chúng ta có thể sử dụng phương pháp SUMIFS với đk “trống” và “không trống” trên dữ liệu thực như thế nào:

Giả sử bạn có ngày mua hàng trong cột B, ngày ship hàng trong cột C và con số trong cột D. Làm cố nào để chúng ta 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ị khớp ứng với các ô ko rỗng vào cột B và các ô 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:

= 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 tác dụng tại vùng B4:D7 theo các điều kiện tương ứng tại cột A (Mã) và chiếc 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 ko thể áp dụng hàm SUMIF cơ mà phải sử dụng hàm SUMIFS (ở đây chúng ta bỏ qua những hàm khác nhưng mà chỉ xét tính ứng dụng của hàm SUMIF/SUMIFS)Điều kiện buộc phải tính không hẳn cố định cơ mà tùy biến, viết 1 hàm SUMIFS tại B4 rồi copy hàm đó lịch sự các ô khác để tínhĐiều khiếu nại Mã và 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 số lượng trong vùng bảng dữ liệu F2:H10Criteria_range1: là cột Mã trong vùng bảng dữ 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 tài 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 kiện tại ô B3

Do đó phương pháp tại ô B4 hoàn toàn có thể viết như sau: