Hướng dẫn sử dụng hàm sumif

3 Sử dụng SUMIFS cùng SUMIF trong Excel – một số vấn đề cần nhớ:4 Làm cầm nào nhằm áp dụng SUMIFS vào Excel – ví dụ công thức5 Sử dụng hàm SUMIF có khá nhiều điều kiện OR6 Excel SUMIFS có tương đối nhiều ĐK OR7 Sử dụng hàm SUM trong số phương pháp mảng

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

Bạn đang xem: Hướng dẫn sử dụng hàm sumif

Hàm SUMIF – cú pháp với biện 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 trên một điều kiện. Chúng tôi đang đàm đạo về cú pháp của nó một cách cụ thể trong bài viết trước, vị vậy, hiện giờ tôi hãy cho chính mình một phiên bản tóm tắt nhanh khô.

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

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

Nlỗi 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. Tuy nhiên, bên trên, công ty chúng tôi bao gồm bảo rằng Excel SUMIF rất có thể được áp dụng để tính tổng những quý hiếm với khá nhiều điều kiện. Làm nuốm như thế nào mà lại có thể được? Thực tế, bạn cần thêm những công dụng của vài hàm SUMIF cùng áp dụng những phương pháp SUMIF với những ĐK mảng, nhỏng vào ví dụ tiếp theo.


*

*

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

quý khách hàng sử dụng hàm SUMIFS vào Excel để kiếm tìm một tính tổng có mức giá trị những quý giá dựa vào những ĐK. Hàm SUMIFS vào excel đã có được giới thiệu vào Excel 2007, bởi vì vậy bạn có thể thực hiện nó vào tất cả những phiên bạn dạng của Excel 2013, 2010 và 2007.

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

SUMIFS (sum_range, criteria_range1, criteria1, , …)

3 đối số trước tiên là bắt buộc, các range được bổ sung cập nhật cùng những ĐK tương quan tới bọn chúng thì được tùy lựa chọn.

sum_range – một hoặc các ô tính tổng, mang ý nghĩa buộc phải. Đây có thể là 1 trong những ô độc nhất, một dải ô hoặc dải có tên. Chỉ tất cả những ô chỉ chứa số mới được tính tổng; Còn cực hiếm ô trống cùng quý hiếm văn uống bản thì sẽ ảnh hưởng bỏ qua.criteria_range1 – range đầu tiên được Review theo các điều kiện tương quan, mang tính chất cần.criteria1 – điều kiện thứ nhất bắt buộc được thỏa mãn nhu cầu, là đối số sẽ phải tất cả. quý khách có thể cung cấp các điều kiện bên dưới dạng một số, biểu thức xúc tích, tmê man chiếu ô, vnạp năng lượng phiên bản hoặc một hàm Excel khác. Ví dụ chúng ta có thể áp dụng những ĐK như 10, “> = 10”, A1, “cherries” hoặc TODAY ().criteria_range2, criteria2, … – đó là những dải được thêm vào cùng ĐK liên quan tới các dải này, bởi chúng ta tùy chọn. quý khách hàng có thể sử dụng về tối đa 127 dải / ĐK vào công thức SUMIFS.

Chú ý: Hàm SUMIFS vào excel hoạt động với biểu thức xú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ộ những ĐK được chỉ định và hướng dẫn là hợp lý cho ô kia.

Và hiện giờ, chúng ta hãy xem hàm SUMIFS vận động cùng với nhị điều kiện. Giả sử bạn bao gồm một bảng liệt kê các lô mặt hàng hoa trái từ các công ty cung cấp không giống nhau. Bạn có tên trái vào cột A, tên của nhà hỗ trợ trong cột B với số lượng trong cột C. quý khách muốn tìm ra số chi phí tương quan mang đến quả cùng bên hỗ trợ, ví dụ: Tất cả táo khuyết (apples) được cung cấp vì chưng Pete.

Khi ai đang hấp thụ một chiếc mớ lạ và độc đáo, bạn nên bước đầu cùng với phần lớn điều đơn giản dễ dàng. Vì vậy, nhằm bước đầu, hãy xác minh toàn bộ những đối số cho cách làm 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 bên trên, cùng bạn sẽ cảm nhận 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ách làm dễ dàng rộng, chúng ta có thể sửa chữa các tiêu chuẩn văn bạn dạng “apples” cùng “Pete” bằng các tsay mê chiếu ô. Trong trường phù hợp này, các bạn sẽ chưa hẳn chuyển đổi công thức để tính toán thù lượng hoa trái không giống xuất phát điểm từ 1 bên hỗ trợ không giống nhau:

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

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

Vì mục tiêu của khuyên bảo này là bao hàm tất cả những biện pháp rất có thể bao gồm, nhằm tính tổng những cực hiếm thỏa một vài ĐK, bọn họ vẫn thảo luận các ví dụ phương pháp với tất cả nhì hàm – SUMIFS cùng SUMIF với rất nhiều ĐK. Để thực hiện chúng một cách đúng chuẩn, bạn phải nắm rõ nhì hàm này có điểm gì tầm thường và phương pháp bọn chúng khác biệt ra làm sao.

Mặc dù nét phổ biến thì tương đối ví dụ – tương đương về điểm đến chọn lựa cho công dụng ở đầu cuối và những tmê mẩn số – trong những khi sự khác biệt mặc dù ko rõ rang cơ mà vẫn siêu cần thiết.

1. Trình từ bỏ của các đối số

Trong các hàm Excel SUMIF cùng SUMIFS, đồ vật tự những đối số là không giống nhau. Cụ thể, sum_range là tđê mê số đầu tiên vào SUMIFS, tuy nhiên lại đứng vị trí thứ 3 vào công thức SUMIF.

lúc bắt đầu học, các bạn sẽ cảm giác dường như nhỏng Microsoft đã ráng ý làm phức tạp hóa cho những người học tập cùng người dùng nó. Tuy nhiên, lúc xem xét kỹ rộng, các bạn sẽ thấy nguyên do thực chất đằng sau nó. Vấn đề là sum_range là tùy lựa chọn vào SUMIF. Nếu các bạn bỏ lỡ nó, không có vấn đề, SUMIF phương pháp của bạn sẽ tính tổng các giá trị vào range (tmê mẩn số đầu tiên).

Trong SUMIFS, sum_range là vô cùng đặc trưng cùng là bắt buộc, với đó là lý do tại sao nó cho trước. Có thể những người của Microsoft cho rằng sau khi thêm các dải/điều kiện chu đáo vật dụng 10 hoặc 100, thì tất cả ai đó rất có thể quên khẳng định dải để tính tổng:)

Tóm lại, nếu khách hàng đang coppy cùng sửa đổi những hàm này, hãy bảo vệ chúng ta đặt các thông số theo sản phẩm trường đoản cú đúng.

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

Trong hàm SUMIF, đối số sum_range ko tuyệt nhất thiết bắt buộc có cùng form size với đối số range, miễn sao các bạn bao gồm ô bên trên phía trái. Trong hàm SUMIFS, từng criteria_range đề xuất đựng cùng một vài mặt hàng với cột như tmê mệt số sum_range.

Xem thêm: Sách Hướng Dẫn Sử Dụng Xe Liberty, Hướng Dẫn Mở Cốp Xe Liberty

ví dụ như, phương pháp = SUMIF (A2: A9, F1, C2: C18) vẫn trả lại hiệu quả đúng vì Excel chỉ coi ô phía trên phía trái vào đối số sum_range (C2 vào ví dụ này là đúng) và kế tiếp bao hàm số cột với sản phẩm y như size của đối số range.

Công thức SUMIFS: = SUMIFS (C2: C9, A2: A9, “apples”, B2: B10, “Pete”) đã báo lỗi # VALUE! vị criter_range2 (B2: B10) không hợp với criteria_range1 (A2: A9) cùng sum_range (C2: C9).

Có vẻ nlỗi bọn họ đã tiếp cận lâm thời hoàn thành định hướng rồi, đề xuất mục sau bọn họ đang đưa thanh lịch 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 riêng cho những người đi làm

Làm nuốm như thế nào để áp dụng SUMIFS vào Excel – ví dụ công thức

Vừa nãy, chúng ta vẫn bàn bạc về một công thức SUMIFS đơn giản dễ dàng cùng với nhị điều kiện vnạp năng lượng phiên bản. Với phương pháp giống như như thế, bạn có thể sử dụng Excel SUMIFS với khá nhiều điều kiện biểu lộ thông qua số, ngày, biểu thức súc tích, cùng những hàm Excel khác.

lấy một ví dụ 1. Công thức SUMIFS với toán thù tử so sánh

Trong bảng cung ứng hoa quả dưới đây, mang sử, bạn muốn tính tổng toàn bộ những lượng hàng đã làm được Mike cung cấp cùng với số lượng. tự 200 trsinh sống lên. Để làm vấn đề đó, bạn sử dụng tân oán tử so sánh “lớn hơn hoặc bằng” (> =) trong những ĐK 2 cùng nhận ra công thức SUMIFS sau:

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

 

*

Lưu ý: Hãy chú ý rằng trong số công thức SUMIFS, các biểu thức lôgic với những toán tử đối chiếu nên luôn luôn luôn luôn được đặt trong vệt nháy knghiền (“”).

Chúng tôi đã đề cập chi tiết tất cả những toán thù tử đối chiếu hoàn toàn có thể Lúc thảo luận về hàm Excel SUMIF, cùng các bọn chúng cũng có thể áp dụng vào điều kiện SUMIFS. Ví dụ: Trả về quý hiếm tổng của toàn bộ các quý giá trong số ô C2: C9 nhưng mà lớn hơn hoặc bởi 200 và nhỏ rộng hoặc bằng 300.

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

Trong ngôi trường vừa lòng bạn muốn tính tổng các quý hiếm với nhiều điều kiện dựa trên ngày bây chừ, hãy thực hiện hàm TODAY () vào ĐK của hàm SUMIFS của bạn, nlỗi được trình bày dưới. Công thức dưới đây tính tổng vốn vào cột D nếu như ngày khớp ứng vào cột C rơi vào tầm khoảng thời hạn 7 ngày vừa rồi, có bao gồm ngày hôm nay:

Hướng dẫn học tập Excel cơ bản

ví dụ như 3. Hàm SUMIFS với ô trống cùng không trống

Lúc phân tích báo cáo 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ị 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 cực hiếm cơ mà gồm ô trống tương ứng (trọn vẹn ko chứa dũ liệu – không bí quyết, và chuỗi bao gồm 0 kí tự)=SUMIFS(C2:C10, A2:A10, “=”, B2:B10, “=”)

Tính tổng mức vốn trong những ô C2:C10 giả dụ những ô tương ứng với nó vào cột A và B là ô hoàn toàn trống.

“”Tính tổng các quý giá tương ứng với những ô trắng “đánh giá và nhận định trực quan”, bao gồm những quý giá đựng những chuỗi rỗng được trả về vày một số 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 những quý hiếm trong những ô C2:C10 (có cùng những điều kiện) như bí quyết nghỉ ngơi bên trên, mà lại tất cả bao hàm các chuỗi trống.

Những ô không trống“”Tính tổng các giá trị mà lại gồm các quý giá tương xứng là các ô không trống, với tất cả bao hàm chuỗi bao gồm chiều lâu năm bằng 0=SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)

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

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

Và hiện nay, hãy cùng coi biện pháp chúng ta cũng có thể sử dụng bí quyết SUMIFS cùng với điều kiện “trống” và “không trống” bên trên dữ liệu thực như thế nào:

Giả sử chúng ta tất cả ngày đặt hàng vào cột B, ngày giao hàng trong cột C và số lượng trong cột D. Làm cố kỉnh nào để bạn tính được tổng thể sản phẩm không được giao? Nghĩa là bạn có nhu cầu biết tổng những quý giá khớp ứng với các ô ko trống rỗng vào cột B với những ô rỗng vào cột C.

Giải pháp là thực hiện bí quyết SUMIFS cùng với 2 điều kiện:

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

*

Sử dụng hàm SUMIF có rất nhiều ĐK OR

Nhỏng vẫn chú ý tại vị trí đầu của bài xích này, thì hàm SUMIFS được thiết kế theo phong cách với AND. Nhưng nếu như bạn yêu cầu tính tổng những cực hiếm có tương đối nhiều ĐK OR, nghĩa là lúc gồm ít nhất một trong các điều kiện sẽ tiến hành đáp ứng?

lấy ví dụ 1. SUMIF + SUMIF

Giải pháp đơn giản dễ dàng độc nhất là tính tổng các công dụng trả về vày một số hàm SUMIF. Ví dụ: cách làm dưới đây đang minch họa cách tính tổng số thành phầm vị Mike cùng John cung cấp:

= SUMIF (C2: C9, “Mike”, D2: D9) + SUMIF (C2: C9, “John”, D2: D9)

*

Nlỗi bạn thấy, hàm SUMIF thứ nhất cho thấy thêm số lượng khớp ứng với “Mike”, còn hàm SUMIF tê thì trả lại số tiền tương quan mang đến “John” và sau đó chúng ta cùng hai công dụng này lại.

lấy một ví dụ 2. SUM & SUMIF cùng với đối số mảng

Các giải pháp bên trên khôn xiết đơn giản và dễ dàng và đích thực kết quả cao khi chỉ bao gồm một vài nét khiếu nại. Nhưng một cách làm SUMIF + SUMIF có thể được cải tiến và phát triển rất nhiều trường hợp bạn có nhu cầu tính tổng các quý giá với khá nhiều điều kiện OR. Trong ngôi trường hợp này, phương pháp tiếp cận xuất sắc rộng là sử dụng một đối số nlỗi điều kiện mảng vào hàm SUMIF, như sau:

quý khách hàng có thể ban đầu bằng cách liệt kê toàn bộ các ĐK của bạn, được phân cách do lốt phẩy cùng tiếp nối đặt nó trong dấu ngoặc nhọn, vấn đề này được Gọi là mảng.

Trong ví dụ trước, nếu bạn có nhu cầu tính tổng các sản phẩm vì John, Mike cùng Pete cung cấp, điều kiện 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 bao gồm 3 cực hiếm sẽ phải bao gồm vào bí quyết SUMIF nhằm trả về cha công dụng chủ quyền, cơ mà vì chưng chúng ta viết phương pháp trong một ô tuyệt nhất, nó vẫn chỉ trả lại công dụng thứ nhất – Tức là tổng cộng sản phẩm vị John hỗ trợ. Để nó hoạt động, bạn đề nghị sử dụng vài mẹo – lồng bí quyết SUMIF của người tiêu dùng trong một hàm SUM, nlỗi sau:

= SUM (SUMIF (C2: C9, “John”, “Mike”, “Pete”, D2: D9))

*

Nhỏng các bạn thấy, một ĐK mảng nlỗi trên sẽ làm cho bí quyết nhỏ tuổi gọn rộng các so với SUMIF + SUMIF, cùng chúng cho phép chúng ta thêm nhiều quý giá nlỗi bạn có nhu cầu vào mảng đó.

Pmùi hương pháp này vẫn chạy cùng với những con số hoặc các cực hiếm văn bạn dạng. Ví dụ: nếu như cố gắng vày các bạn mang tên những nhà cung cấp vào cột C, nhưng chúng ta chỉ bao gồm ID đơn vị hỗ trợ như một, 2, 3, v.v … thì phương pháp SUMIF của bạn sẽ nhỏng sau:

= SUM (SUMIF (C2: C9, 1,2,3, D2: D9))

Không giống hệt như những quý hiếm văn bạn dạng, các vô danh cần phải được bao gồm trong vết nháy kxay vào đối số mảng.

Ví dụ 3. SUMPRODUCT & SUMIF

Trong ngôi trường hợp, quý khách hàng ham mê liệt kê những điều kiện trong vài ba ô rứa vị hướng dẫn và chỉ định chúng trực tiếp vào bí quyết, thì bạn có thể áp dụng SUMIF kết phù hợp với hàm SUMPRODUCT để nhân nhiều yếu tố trong các mảng xác minh rồi trả lại tổng số lượng của những thành phầm đó.

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

Trong trường phù hợp G2: G4 là những ô đựng điều kiện của khách hàng, thì ithương hiệu bên cung cấp vào trường hợp này, được minh họa như trong hình họa chụp màn hình hiển thị dưới đây:

Nhưng tất nhiên, không tồn tại gì ngăn uống cản nếu bạn có nhu cầu liệt kê các giá trị trong một điều kiện mảng của hàm SUMIF”

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

Kết trái trả về vị cả hai bí quyết đang y hệt như gần như gì các bạn thấy vào hình:

*

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

Nếu bạn có nhu cầu tính tổng có ĐK các giá trị vào Excel thì không chỉ tương quan cho tới những ĐK OR, tuy nhiên với một số trong những ĐK, các bạn sẽ phải sử dụng SUMIFS chũm vị SUMIF. Các cách làm sẽ tựa như những gì mà bọn họ vừa bàn thảo.

Xem thêm: Hướng Dẫn Làm Sữa Đậu Nành

Nhỏng thường xuyên lệ, một ví dụ hoàn toàn có thể giúp minch họa tốt hơn điều bên trên. Trong bảng những bên cung ứng trái cây của Cửa Hàng chúng tôi, hãy thêm Delivery Date (Ngày giao hàng) (cột E) và tìm kiếm tổng số lượng được Mike, John và Pete giao hồi tháng 10.

Ví dụ 1. SUMIFS + SUMIFS

Công thức được tạo ra bởi vì phương pháp phương pháp này bao hàm nhiều lần lặp đi lặp lại với trông có vẻ xuề xòa, mặc dù vậy dẫu vậy nó rất dễ nắm bắt, và điểm quan trọng nhất là nó vận động giỏi
Chuyên mục: Kiến Thức Bổ Ích