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

Bạn đang xem: Hướng dẫn sử dụng hàm sumifs trong excel 2010


Sử dụng SUMIFS và SUMIF vào Excel – một vài điều yêu cầu nhớ:Làm cố gắng nào để sử dụng SUMIFS trong Excel – lấy ví dụ như công thứcSử dụng hàm SUMIF có tương đối nhiều điều kiện 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à biện pháp sử dụng:

Hàm SUMIF được áp dụng để tính tổng tất cả đ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, do 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, )

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

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

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

Bạn sử dụng hàm SUMIFS vào Excel để tìm một tính tổng có giá trị các giá trị dựa vào nhiều điều kiện. Hàm SUMIFS trong excel đang được reviews trong Excel 2007, bởi vì vậy chúng ta 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 cùng với SUMIF, thì cú pháp SUMIFS phức hợp 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 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 ý nghĩa 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 tất cả tên. Chỉ có các ô chỉ cất số mới được tính tổng; Còn cực hiếm ô trống và quý hiếm văn bản thì sẽ bị bỏ qua.criteria_range1 – range đầu tiên được nhận xét theo các điều kiện 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ố sẽ 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ụ bạn cũng có thể sử dụng những đ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à điều kiện liên quan tiền tới những dải này, do chúng ta tùy chọn. Bạn cũng có thể sử dụng tối đa 127 dải / đk trong cách làm SUMIFS.

Chú ý: Hàm SUMIFS trong excel vận động với biểu thức ngắn gọn 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 tất cả các đk được chỉ định và hướng dẫn là đúng cho ô đó.

Và bây giờ, họ hãy coi hàm SUMIFS vận động với hai đ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ừ các nhà cung cấp khác nhau. Bạn có tên quả vào 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 liên quan đến quả với nhà cung cấp, ví dụ: tất cả táo (apples) được hỗ trợ bởi Pete.

*

Khi nhiều người đang tiếp thu một cái mới mẻ, chúng ta nên ban đầu với hầu như điều 1-1 giản. Do 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 giờ tập hòa hợp các thông số 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 dễ dàng và đơn giản hơn, chúng ta cũng có thể thay thế các tiêu chuẩn văn bạn dạng “apples” với “Pete” bằng các tham chiếu ô. Vào trường vừa lòng này, các bạn sẽ không phải thay đổi công thức để thống kê giám sát lượng hoa trái 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 các bạn theo dõi 2 videos tiếp 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 đã giúp chúng ta 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 vào Excel – một trong những điều cần nhớ:

Vì mục đích của khuyên bảo này là bao gồm tất cả các cách có thể có, nhằm tính tổng những giá trị thỏa một số trong những điều kiện, chúng ta sẽ bàn luận các lấy một ví dụ công thức đối 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 phải hiểu rõ hai hàm này có điểm gì chung và cách thức chúng khác biệt như chũm nào.

Mặc mặc dù nét chung thì khá rõ ràng – tương đồng về điểm đến lựa chọn cho hiệu quả cuối thuộc và những tham số – trong những khi sự biệt lập dù không rõ rang dẫu vậy vẫn rất phải 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. Vắt thể, sum_range là tham số trước tiên trong SUMIFS, cơ mà lại đứng thứ 3 trong cách làm SUMIF.

Xem thêm: Những Bài Hát Tiếng Nhật Hay Về Tình Yêu Thật Nhiều Umi, Top 100 Nhạc Nhật Nhẹ Nhàng Tình Cảm

Khi ban đầu học, các bạn sẽ cảm thấy có vẻ như Microsoft đã rứa ý làm tinh vi hóa cho những người học và 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, các bạn sẽ thấy tại sao thực chất đằng sau nó. Vấn đề là sum_range là tùy lựa chọn trong SUMIF. Nếu như bạn bỏ qua nó, không có vấn đề, SUMIF công thức của các 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 đặc trưng và là bắt buộc, cùng đó là lý do tại sao nó mang lại trước. Hoàn toàn có thể những người của Microsoft suy nghĩ rằng sau thời điểm thêm các dải/điều kiện lưu ý thứ 10 hoặc 100, thì tất cả ai đó hoàn toàn có thể quên xác minh dải nhằm tính tổng:)

Tóm lại, nếu như bạn đang xào luộc và chỉnh sửa các hàm này, hãy đảm bảo bạn đặt các thông số kỹ thuật theo máy 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 size với đối số range, miễn là bạn có ô bên trên bên trái. Trong hàm SUMIFS, mỗi criteria_range buộc phải chứa cùng một số trong những hàng và cột như thông số sum_range.

Ví dụ, cách làm = SUMIF (A2: A9, F1, C2: C18) sẽ trả lại hiệu quả đúng vì chưng 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ới hàng y 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ư chúng ta đã tiếp cận tạm hoàn thành lý thuyết rồi, đề xuất mục sau họ sẽ chuyển sang làm việc thực hành (chính là các 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 cố gắng nào để áp dụng SUMIFS trong Excel – ví dụ công thức

Vừa nãy, bọn họ đã luận bàn về một công thức SUMIFS đơn giản với hai điều kiện văn bản. Với cách thức tương tự như thế, chúng ta cũng có thể sử dụng Excel SUMIFS với tương đối nhiều điều kiện thể hiện bằng số, ngày, biểu thức logic, và các hàm Excel khác.

Ví dụ 1. Bí quyết SUMIFS với toán tử so sánh

Trong bảng hỗ trợ trái cây bên dưới đây, đưa sử, bạn muốn tính tổng toàn bộ các lượng hàng đã có được Mike hỗ trợ với số lượng. Từ 200 trở lên. Để làm cho điều này, bạn sử dụng toán tử đối chiếu “lớn rộng hoặc bằng” (> =) trong các điều kiện 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 để ý rằng trong số công thức SUMIFS, những biểu thức lôgic với các toán tử đối chiếu phải luôn luôn luôn được đặt trong lốt nháy kép (“”).

Chúng tôi vẫn đề cập chi tiết tất cả các toán tử so sánh có thể khi trao đổi về hàm Excel SUMIF, và các chúng cũng có thể sử dụng trong điều kiện 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ởi 200 và nhỏ tuổi 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 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 vào ngày hiện nay 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 tiếp sau đây tính tổng giá trị trong cột D nếu như ngày khớp ứng trong cột C lâm vào cảnh khoảng thời hạn 7 ngày vừa qua, có bao gồm ngày hôm nay:

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

Tính tổng giá trị trong những ô C2:C10 nếu những ô tương xứng với nó vào cột A cùng B là ô trọn vẹn trống.

“”Tính tổng những giá trị khớp ứng với các ô trắng “nhận định trực quan”, bao hà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ố 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 những giá trị trong số ô C2:C10 (có cùng các điều kiện) như cách làm ở trên, tuy vậy có bao hàm các chuỗi trống.

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

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

Giả sử chúng ta có ngày đặt đơn hàng trong cột B, ngày giao hàng trong cột C và số lượng trong cột D. Làm vậy nào để các 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 những giá trị tương xứng với những ô không rỗng vào cột B và những ô rỗng trong cột C.

Giải pháp là áp 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: cách viết đk hàm SUMIFS

Đề bài: Cho bảng dữ 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 khiếu nại tương ứng trên cột A (Mã) và dòng 3 (Ngày)Bước 1: Phân tích đề bài

Đề bài xích yêu cầu tính tổng theo 2 điều kiện là Ngày cùng Mã, do đó ta ko thể sử dụng hàm SUMIF nhưng mà phải áp dụng hàm SUMIFS (ở đây bọn 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 buộc phải tính chưa hẳn cố định mà lại tùy biến, viết 1 hàm SUMIFS tại B4 rồi copy hàm đó thanh lịch 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 phương pháp 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à điều khiếu nại về Mã (để tương ứ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 kiện về Ngày (tương ứng cùng với Criteria_range2), với ô B4 là điều khiếu nại tại ô B3

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