Hàm sumif nhiều điều kiện
Trong bài viết này, học tập Excel Online sẽ lý giải sự biệt lập 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, bên cạnh đó, cũng cung cấp một số ví dụ như về công thức để 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) vào phiên bạn dạng Excel 2013, 2010, 2007, 2003 và trước nữa.
Bạn đang xem: Hàm sumif nhiều điều kiện
Sử dụng SUMIFS và SUMIF trong Excel – một số trong những điều đề xuất nhớ:Làm vắt nào để sử dụng SUMIFS vào Excel – ví dụ như công thứcSử dụng hàm SUMIF có khá nhiều điều khiếu nại ORExcel SUMIFS có khá nhiều điều kiện ORSử dụng hàm SUM trong những công thức mảng
Hàm SUMIF – cú pháp và biện pháp sử dụng:
Hàm SUMIF được áp dụng để tính tổng gồm điều kiện, dựa trên một điều kiện. Công ty chúng tôi đã bàn luận 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, hiện thời tôi hãy cho chính mình một bạn dạng tóm tắt nhanh.
SUMIF (range, điều kiệ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. Tuy nhiên, mặt trên, cửa hàng chúng tôi có bảo rằng Excel SUMIF rất có thể được thực hiện để tính tổng những giá trị với khá nhiều điều kiện. Làm chũm nào mà có thể được? Thực tế, bạn cần thêm các tác dụng của vài hàm SUMIF với 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ụ 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ị các giá trị dựa trên nhiều điều kiện. Hàm SUMIFS trong excel vẫn được reviews trong Excel 2007, vày vậy bạn cũng có thể sử dụng nó trong tất cả các phiên phiên bản của Excel 2013, 2010 với 2007.
So cùng 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, các 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 nhiều ô tính tổng, mang ý nghĩa bắt buộc. Đây có thể là một ô duy nhất, một dải ô hoặc dải gồm tên. Chỉ có những ô chỉ chứa số mới được tính tổng; Còn quý hiếm ô trống và quý giá văn bạn dạng thì có khả năng sẽ bị bỏ qua.criteria_range1 – range thứ nhất được review theo những điều kiện 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ố sẽ phải có. Bạn cũng 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ụ 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 thêm vào và đk liên quan lại tới các dải này, do bạn tùy chọn. Bạn cũng có thể sử dụng buổi tối đa 127 dải / đk trong phương pháp SUMIFS.
Chú ý: Hàm SUMIFS vào excel chuyển độ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 tính tổng nếu tất cả các đk được chỉ định và hướng dẫn là chuẩn cho ô đó.
Và bây giờ, chúng ta hãy coi hàm SUMIFS vận động với nhì điều kiện. Trả sử chúng ta có một bảng liệt kê những lô sản phẩm trái cây từ các nhà hỗ trợ khác nhau. Bạn mang tên quả trong cột A, tên của phòng cung cấp cho trong cột B và con số trong cột C. Bạn có nhu cầu tìm ra số tiền liên 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 cái mới mẻ, các bạn nên bắt đầu với phần đông điều đối chọi giản. Vì chưng vậy, nhằm bắt đầu, hãy xác định tất cả những đối số cho bí quyết SUMIFS của bọn chúng tôi:
sum_range – C2: C9criteria_range1 – A2: A9criteria1 – “apples”criteria_range2 – B2: B9criteria2 – “Pete”Bây tiếng tập vừa lòng các thông số kỹ thuật trên, và bạn sẽ nhận được bí quyết SUMIFS sau:
= SUMIFS (C2: C9, A2: A9, "táo", B2: B9, "Pete")
Để khiến cho việc sửa đổi công thức đơn giản và dễ dàng hơn, bạn có thể thay thế những tiêu chuẩn chỉnh văn bạn dạng “apples” với “Pete” bằng các tham chiếu ô. Trong trường hòa hợp này, các bạn sẽ không phải thay đổi công thức để giám sát lượng hoa quả khác xuất phát điểm từ một nhà hỗ trợ khác nhau:
= SUMIFS (C2: C9, A2: A9, F1, B2: B9, F2)
Mời chúng ta theo dõi 2 videos sau đây và nhớ rằng download tư 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 đã giúp các bạn sử dụng các ký tự vệt sao cùng hỏi chấm để tính tổng nâng cao:
Sử dụng SUMIFS và SUMIF trong Excel – một số điều nên nhớ:
Vì mục tiêu của khuyên bảo này là bao hàm tất cả những cách hoàn toàn có thể có, để tính tổng những giá trị thỏa một số điều kiện, họ sẽ thảo luận các lấy ví dụ như công thức với cả hai hàm – SUMIFS cùng SUMIF với khá nhiều điều kiện. Để thực hiện chúng một cách thiết yếu xác, bạn cần phải hiểu rõ nhì hàm này có điểm gì tầm thường và phương thức chúng khác nhau như vậy nào.
Mặc mặc dù nét chung thì khá cụ thể – tương đồng về điểm đến chọn lựa cho kết quả cuối cùng và các tham số – trong khi sự khác biệt dù không rõ rang tuy nhiên vẫn rất đề xuất thiết.
1. Trình tự của những đối số
Trong các hàm Excel SUMIF và SUMIFS, lắp thêm tự các đối số là không giống nhau. Nạm thể, sum_range là tham số thứ nhất trong SUMIFS, tuy thế lại đứng vị trí thứ 3 trong cách làm SUMIF.
Xem thêm: Tan Nhà Nát Cửa Vì Mê Chút Tiền Lời Từ "Bốc Bát Họ", Video Full Bão Qua Làng Tập 9 Ngày 20/8
Khi ban đầu học, các bạn sẽ cảm thấy có vẻ như Microsoft đã vắt ý làm phức hợp hóa cho những người học và người tiêu dùng nó. Mặc dù nhiên, lúc chứng kiến tận mắt xét kỹ hơn, các bạn sẽ thấy lý do thực chất đằng sau nó. Vụ việc là sum_range là tùy lựa chọn trong SUMIF. Nếu bạn bỏ qua nó, không có vấn đề, SUMIF cách làm của bạn sẽ tính tổng các 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, cùng đó là vì sao tại sao nó cho trước. 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 cẩn thận thứ 10 hoặc 100, thì có ai đó có thể quên xác định dải để tính tổng:)
Tóm lại, nếu bạn đang xào nấu và chỉnh sửa các hàm này, hãy bảo đảm bạn để các thông số theo trang bị tự đúng.
2. Kích thước của đối số sum_range cùng criteria_range
Trong hàm SUMIF, đối số sum_range không tốt nhất thiết phải gồm cùng form size với đối số range, miễn là các bạn có ô phía trên bên trái. Vào hàm SUMIFS, mỗi criteria_range phải chứa cùng một vài hàng cùng cột như tham số sum_range.
Ví dụ, cách làm = SUMIF (A2: A9, F1, C2: C18) sẽ trả lại hiệu quả đúng vị Excel chỉ coi ô phía trên bên trái vào đối số sum_range (C2 trong ví dụ này là đúng) và sau đó bao hà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ư họ đã tiếp cận tạm ngừng lý thuyết rồi, phải mục sau chúng ta sẽ chuyển sang thao tác làm việ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 tất cả những người đi làm
Làm nắm nào để thực hiện SUMIFS trong Excel – ví dụ công thức
Vừa nãy, họ đã đàm luận về một bí quyết SUMIFS đơn giản với hai điều kiện văn bản. Với cách thức tương từ bỏ như thế, bạn 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. Cách làm SUMIFS với toán tử so sánh
Trong bảng cung ứng trái cây bên dưới đây, đưa sử, bạn có nhu cầu 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 sử dụng toán tử đối chiếu “lớn rộng hoặc bằng” (> =) trong những điều kiện 2 với nhận được cách làm SUMIFS sau:
= SUMIFS (C2: C9, B2: B9, “Mike”, C2: C9, “> = 200”)
Lưu ý: Hãy xem xét rằng trong số công thức SUMIFS, các biểu thức lôgic với các 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 vẫn đề cập cụ thể tất cả các toán tử so sánh hoàn toàn có thể khi đàm đạo về hàm Excel SUMIF, và những chúng cũng rất có thể sử dụng trong điều kiện SUMIFS. Ví dụ: Trả về giá trị tổng của toàn bộ các giá bán trị trong những ô 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. Thực hiện 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 rất nhiều điều kiện dựa trên ngày hiện tại tại, hãy áp dụng hàm TODAY () trong đk của hàm SUMIFS của bạn, như được trình diễn 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 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à tài liệu khác, các bạn thường cần được tính tổng những giá trị tương ứng với ô trống hoặc ko trống. 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à B là ô trọn vẹn trống. Tính tổng những giá trị trong các ô C2:C10 (có cùng những điều kiện) như phương pháp ở trên, tuy vậy có bao hàm các chuỗi trống. Tính tổng giá chỉ trị trong các ô C2:C10 nếu những ô tương xứng với nó vào cột A với B ko là ô trống, có bao gồm các ô cùng với chuỗi trống. =SUM((C2:C10) * (LEN(A2:A10)>0)*(LEN(B2:B10)>0)) Tính tổng giá chỉ trị trong số ô C2:C10 nếu những ô tương ứng với nó vào cột A cùng B ko là ô trống, cùng không bao hà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ó thể sử dụng bí quyết SUMIFS với điều kiện “trống” và “không trống” trên dữ liệu thực như thế nào:Đ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à bao gồm ô trống khớp ứng (hoàn toàn không cất dũ liệu – không công thức, với chuỗi bao gồm 0 kí tự) =SUMIFS(C2:C10, A2:A10, “=”, B2:B10, “=”) “” Tính tổng các giá trị khớp ứng với những ô white “nhận định trực quan”, bao gồm các cực hiếm chứa các chuỗi trống rỗng được trả về bởi một số hàm Excel không giống (ví dụ: ô có công thức như = “”). =SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”) Những ô ko trống “” Tính tổng những giá trị nhưng mà có những giá trị khớp ứng là các ô không trống, cùng có bao hàm chuỗi có chiều dài bằng 0 =SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”) SUM-SUMIFhaySUM / LEN Tí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ới không bao gồm chuỗi gồm chiều dài bởi 0 =SUM(C2:C10) – SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)
Giả sử bạn có ngày đặt hàng trong cột B, ngày phục vụ trong cột C và số lượng trong cột D. Làm vậy nào để bạn tính được tổng số sản phẩm chưa được giao? Nghĩa là bạn muốn biết tổng các giá trị tương xứng với những ô 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: bí quyết 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 hiệu quả tại vùng B4:D7 theo các điều kiện tương ứng tại cột A (Mã) và mẫu 3 (Ngày)Bước 1: Phân tích đề bài
Đề bài bác yêu mong tính tổng theo 2 điều khiếu nại là Ngày cùng Mã, do đó ta không thể thực hiện hàm SUMIF mà lại phải áp 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 đề xuất tính không phải cố định cơ mà tùy biến, viết 1 hàm SUMIFS tại B4 rồi copy hàm đó quý phái các ô khác để tínhĐiều khiếu nại Mã cùng Ngày có thể thay đổi trong 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ã vào vùng bảng dữ liệu F2:H10Criteria1: là điều kiện 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 trong vùng bảng tài liệu F2:H10Criteria2: là điều khiếu nại về Ngày (tương ứng với Criteria_range2), với ô B4 là điều khiếu nại tại ô B3Do đó bí quyết tại ô B4 rất có thể viết như sau:
