CÁCH SỬ DỤNG CÔNG THỨC SUMIFS

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

Bạn đang xem: Cách sử dụng công thức sumifs


Sử dụng SUMIFS và SUMIF vào Excel – một số trong những vấn đề cần nhớ:Làm nắm như thế nào để áp dụng SUMIFS trong Excel – ví dụ công thứcSử dụng hàm SUMIF có rất nhiều ĐK ORExcel SUMIFS có không ít điều kiện ORSử dụng hàm SUM trong số bí quyết mảng

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

Hàm SUMIF được sử dụng để tính tổng có ĐK, dựa vào một điều kiện. Chúng tôi đang đàm đạo về cú pháp của chính nó một biện pháp chi tiết trong bài viết trước, do vậy, bây giờ tôi hãy cho chính mình một bạn dạng tóm tắt nhanh hao.

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

range – dải của những ô được đánh giá theo điều kiện nhưng bạn chỉ dẫn, mang tính yêu cầu.criteria – điều kiện cần phải đáp ứng nhu cầu, mang tính bắt buộcsum_range – những ô tính tổng giả dụ thỏa ĐK, mang tính chất tùy chọn.

Nhỏng chúng ta thấy, cú pháp của hàm Excel SUMIF chỉ cho phép một ĐK. Tuy nhiên, bên trên, Cửa Hàng chúng tôi gồm nói rằng Excel SUMIF có thể được sử dụng nhằm tính tổng những giá trị với tương đối nhiều ĐK. Làm nắm nào nhưng mà có thể được? Thực tế, bạn cần thêm các tác dụng của vài ba hàm SUMIF với sử dụng những bí quyết SUMIF cùng với những điều kiện mảng, nlỗi vào ví dụ tiếp sau.

Hàm SUMIFS – cú pháp và biện pháp sử dụng:

Bạn sử dụng hàm SUMIFS vào Excel nhằm tra cứu một tính tổng có giá trị những cực hiếm dựa trên các ĐK. Hàm SUMIFS trong excel đã có trình làng vào Excel 2007, vì vậy chúng ta cũng có thể áp dụng nó trong tất cả những phiên phiên bản của Excel 2013, 2010 với 2007.

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

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

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


*

*

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

Chụ ý: Hàm SUMIFS trong excel vận động với biểu thức ngắn gọn xúc tích cùng 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 ĐK được hướng đẫn là chuẩn cho ô đó.

Và hiện nay, bọn họ hãy coi hàm SUMIFS vận động cùng với hai điều kiện. Giả sử bạn bao gồm một bảng liệt kê các lô hàng hoa trái tự những bên cung ứng khác nhau. Bạn mang tên trái trong cột A, tên ở trong nhà cung ứng vào cột B và con số trong cột C. Quý Khách ao ước tìm thấy số tiền liên quan cho trái với bên hỗ trợ, ví dụ: Tất cả táo bị cắn (apples) được hỗ trợ vị Pete.

*

khi nhiều người đang thu nạp một cái mới mẻ và lạ mắt, chúng ta nên ban đầu cùng với gần như điều đơn giản dễ dàng. Vì vậy, nhằm bắt đầu, hãy khẳng định toàn bộ 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 giờ tập phù hợp những thông số kỹ thuật bên trên, cùng bạn sẽ nhận thấy bí quyết SUMIFS sau:

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

*

Để khiến cho bài toán chỉnh sửa bí quyết dễ dàng hơn, bạn cũng có thể thay thế các tiêu chuẩn chỉnh vnạp năng lượng bản “apples” và “Pete” bởi các tsay mê chiếu ô. Trong trường hợp này, các bạn sẽ không hẳn đổi khác công thức nhằm tính toán lượng hoa trái khác xuất phát điểm từ 1 đơn vị cung cấp không giống nhau:

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

Mời các bạn theo dõi và quan sát 2 videos dưới đây và đừng quên tải về tư liệu cố nhiên dưới nhằm làm rõ rộng về hàm SUMIFS nhé!

Video cải thiện về 2 hàm SUMIF và SUMIFS để giúp đỡ chúng ta áp dụng những cam kết từ lốt sao và hỏi chnóng nhằm tính tổng nâng cao:


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

Vì mục tiêu của trả lời này là bao gồm tất cả các biện pháp rất có thể có, để tính tổng những quý giá thỏa một vài ĐK, họ vẫn luận bàn những ví dụ bí quyết với cả hai hàm – SUMIFS cùng SUMIF với tương đối nhiều điều kiện. Để sử dụng bọn chúng một phương pháp chính xác, bạn cần phải nắm rõ nhị hàm này còn có điểm gì thông thường cùng phương pháp bọn chúng khác biệt thế nào.

Mặc dù đường nét bình thường thì hơi ví dụ – tương đồng về điểm đến chọn lựa cho công dụng sau cuối với những ttê mê số – trong khi sự khác hoàn toàn mặc dù ko rõ rang cơ mà vẫn cực kỳ cần thiết.

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

Trong các hàm Excel SUMIF với SUMIFS, đồ vật trường đoản cú những đối số là không giống nhau. Cụ thể, sum_range là tđê mê số thứ nhất trong SUMIFS, cơ mà lại đứng thứ 3 trong phương pháp SUMIF.

Xem thêm: Dành Riêng Cho Người Việt Ở Nước Ngoài, Võ Lâm Truyền Kỳ 2

lúc ban đầu học, các bạn sẽ cảm giác có vẻ nhỏng Microsoft đã gắng ý làm phức hợp hóa cho người học tập cùng người tiêu dùng nó. Tuy nhiên, lúc chứng kiến tận mắt xét kỹ hơn, bạn sẽ thấy lý 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 chúng ta bỏ lỡ nó, không tồn tại vấn đề, SUMIF phương pháp của bạn sẽ tính tổng những quý hiếm trong range (tsay đắm số đầu tiên).

Trong SUMIFS, sum_range là cực kỳ đặc biệt quan trọng và là bắt buộc, với chính là nguyên do vì sao nó mang lại trước. cũng có thể những người của Microsoft cho rằng sau khi thêm những dải/điều kiện chú ý đồ vật 10 hoặc 100, thì gồm ai kia hoàn toàn có thể quên xác định dải để tính tổng:)

Tóm lại, nếu bạn vẫn xào nấu và chỉnh sửa các hàm này, hãy bảo vệ các bạn đặt các thông số theo lắp thêm trường đoản cú đúng.

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

Trong hàm SUMIF, đối số sum_range ko độc nhất vô nhị thiết bắt buộc bao gồm cùng kích cỡ với đối số range, miễn là chúng ta có ô bên trên phía bên trái. Trong hàm SUMIFS, mỗi criteria_range nên chứa thuộc một vài sản phẩm với cột như tđam mê số sum_range.

lấy ví dụ, phương pháp = SUMIF (A2: A9, F1, C2: C18) sẽ trả lại tác dụng đúng do Excel chỉ xem ô phía bên trên phía trái trong đối số sum_range (C2 vào ví dụ này là đúng) và sau đó bao hàm số cột cùng mặt hàng giống như kích cỡ của đối số range.

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

Có vẻ nhỏng bọn họ đang tiếp cận nhất thời xong lý thuyết rồi, nên mục sau họ đã chuyển sang thao tác thực hành (đó là các ví dụ công thức:)

Đăng ký kết ngay: Học Excel dành riêng cho tất cả những người đi làm

Làm vắt làm sao nhằm áp dụng SUMIFS trong Excel – ví dụ công thức

Vừa nãy, bọn họ đã bàn bạc về một bí quyết SUMIFS đơn giản dễ dàng cùng với hai ĐK vnạp năng lượng bản. Với phương pháp giống như như vậy, bạn cũng có thể thực hiện Excel SUMIFS với tương đối nhiều điều kiện biểu thị bằng số, ngày, biểu thức xúc tích, với các hàm Excel khác.

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

Trong bảng cung cấp hoa quả sau đây, giả sử, bạn có nhu cầu tính tổng tất cả các lượng mặt hàng đã làm được Mike cung cấp với số lượng. trường đoản cú 200 trnghỉ ngơi lên. Để làm điều này, bạn thực hiện tân oán tử đối chiếu “lớn hơn hoặc bằng” (> =) trong những ĐK 2 với nhận được công thức SUMIFS sau:

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

 

*

Lưu ý: Hãy để ý rằng trong những công thức SUMIFS, những biểu thức lôgic cùng với các toán tử đối chiếu buộc phải luôn luôn luôn luôn được đặt vào vết nháy kxay (“”).

Chúng tôi đang nói chi tiết tất cả các tân oán tử so sánh có thể khi bàn thảo về hàm Excel SUMIF, cùng những bọn chúng cũng rất có thể thực hiện vào ĐK SUMIFS. Ví dụ: Trả về cực hiếm tổng của tất cả những quý hiếm trong số ô C2: C9 nhưng lớn hơn hoặc bởi 200 cùng nhỏ dại rộng hoặc bởi 300.

= SUMIFS (C2: C9, C2: C9, “> = 200”, C2: C9, “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 có nhu cầu tính tổng các cực hiếm với nhiều điều kiện dựa vào ngày bây giờ, hãy sử dụng hàm TODAY () trong điều kiện của hàm SUMIFS của người tiêu dùng, nlỗi được trình bày bên dưới. Công thức dưới đây tính tổng giá trị trong cột D nếu ngày tương ứng vào cột C rơi vào tầm thời gian 7 ngày vừa mới rồi, có bao gồm ngày hôm nay:

Khi so sánh report với tài liệu khác, bạn thường cần được tính tổng các quý giá khớp ứng cù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 quý giá cơ mà tất cả ô trống tương ứng (hoàn toàn không cất dũ liệu – ko cách làm, và chuỗi bao gồm 0 kí tự)=SUMIFS(C2:C10, A2:A10, “=”, B2:B10, “=”)

Tính tổng mức trong những ô C2:C10 trường hợp các ô tương ứng cùng với nó trong cột A cùng B là ô trọn vẹn trống.

“”Tính tổng các giá trị tương xứng cùng với các ô White “nhận định trực quan”, bao gồm các cực hiếm cất các chuỗi trống rỗng được trả về bởi vì một vài hàm Excel không giống (ví dụ: ô gồm phương pháp nlỗi = “”).=SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)

Tính tổng những quý hiếm trong các ô C2:C10 (bao gồm thuộc các điều kiện) như bí quyết ngơi nghỉ trên, mà lại bao gồm bao hàm các chuỗi trống.

Những ô ko trống“”Tính tổng các quý hiếm cơ mà có các quý hiếm khớp ứng là các ô ko trống, và gồm bao hàm chuỗi gồm chiều lâu năm bởi 0=SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)

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

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

Và bây giờ, hãy cùng coi bí quyết chúng ta cũng có thể thực hiện cách làm 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:

Giả sử bạn tất cả ngày đặt hàng vào cột B, ngày Ship hàng vào cột C với số lượng vào cột D. Làm nuốm làm sao để bạn tính được tổng số thành phầm không được giao? Nghĩa là bạn muốn biết tổng những cực hiếm khớp ứng cùng với những ô ko rỗng vào cột B cùng các ô rỗng trong cột C.

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

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

*

Ví dụ 4: Cách viết điều kiện hàm SUMIFS

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

Đề bài xích thưởng thức tính tổng theo 2 ĐK là Ngày với Mã, do đó ta cần thiết sử dụng hàm SUMIF nhưng bắt buộc áp dụng hàm SUMIFS (ở phía trên họ làm lơ các hàm khác nhưng mà chỉ xét tính áp dụng của hàm SUMIF/SUMIFS)Điều khiếu nại đề xuất tính không phải cố định nhưng tùy đổi mới, viết 1 hàm SUMIFS trên B4 rồi copy hàm đó thanh lịch các ô khác nhằm tínhĐiều kiện Mã và Ngày hoàn toàn có thể thay thay đổi vào bí quyết.

Bước 2: Xây dựng bí quyết 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 tài liệu F2:H10Criteria1: là ĐK 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 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 đó bí quyết tại ô B4 hoàn toàn có thể viết như sau: