Trong bài viết này, học Excel Online sẽ phân tích và lý giải sự khác biệt giữa các hàm SUMIF và SUMIFS trong excel theo cú pháp cùng cách sử dụng của chúng, sát bên đó, cũng hỗ trợ một số lấy ví dụ về bí quyết để tính tổng các giá trị có tương đối nhiều điều khiếu nại đồng thời xẩy ra (AND) hoặc toàn bộ không đồng thời xẩy ra (OR) trong phiên phiên bản Excel 2013, 2010, 2007, 2003 với trước nữa.Bạn đã xem: cách dùng hàm sumif với 2 điều kiện

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

Hàm SUMIF được sử dụng để tính tổng bao gồm điều kiện, dựa trên một điều kiện. Cửa hàng chúng tôi đã trao đổi về cú pháp của nó một cách cụ thể trong bài viết trước, bởi vì vậy, bây giờ tôi hãy cho bạn một bản tóm tắt nhanh.

Bạn đang xem: Cách dùng hàm sumif với 2 điều kiện

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

range – dải của những ô được đánh giá theo đk mà các bạn đưa ra, mang tính bắt buộc.criteria – điều kiện rất cần phải đáp ứng, mang tính bắt buộcsum_range – những ô tính tổng ví như thỏa điều kiện, mang ý nghĩa 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. Tuy nhiên, bên trên, công ty chúng tôi có bảo rằng Excel SUMIF rất có thể được thực hiện để tính tổng các giá trị với tương đối nhiều điều kiện. Làm cầm nào mà có thể được? Thực tế, bạn cần thêm các công dụng của vài 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à cách sử dụng:

Bạn sử dụng hàm SUMIFS trong Excel để tìm một tính tổng có giá trị những giá trị dựa trên nhiều điều kiện. Hàm SUMIFS trong excel vẫn được ra mắt 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 của Excel 2013, 2010 cùng 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ố thứ nhất là bắt buộc, những range được bổ sung và những điều kiện liên quan tới bọn chúng thì được tùy chọn.


*

*

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

Chú ý: Hàm SUMIFS vào excel vận động với biểu thức lô ghích với AND, nghĩa là từng ô trong đối số sum_range chỉ được tính tổng nếu toàn bộ các đk được hướng đẫn là hợp lý cho ô đó.

Và bây giờ, chúng ta hãy xem hàm SUMIFS hoạt động với hai điều kiện. Mang sử chúng ta có một bảng liệt kê những lô hàng trái cây từ các nhà cung cấp khác nhau. Bạn mang tên quả vào cột A, tên ở trong phòng cung cung cấp trong cột B và số lượng trong cột C. Bạn có nhu cầu tìm ra số tiền tương 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 chiếc mới mẻ, các bạn nên ban đầu với phần đa điều 1-1 giản. Bởi vì vậy, để bắt đầu, hãy xác minh tất cả các đối số cho phương pháp 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 phù hợp các thông số trên, và các bạn sẽ nhận được cách làm SUMIFS sau:

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


*

Để khiến việc sửa đổi công thức đơn giản dễ dàng 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 những tham chiếu ô. Vào trường vừa lòng này, bạn sẽ không phải đổi khác công thức để đo lường và tính toán lượng hoa trái khác từ 1 nhà cung ứng 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ớ là download tư liệu kèm theo bên dưới để làm rõ hơn về hàm SUMIFS nhé!

Video cải thiện về 2 hàm SUMIF cùng SUMIFS sẽ giúp chúng ta sử dụng các ký tự lốt sao và hỏi chấm nhằm tính tổng nâng cao:

Sử dụng SUMIFS với SUMIF vào Excel – một vài điều phải nhớ:

Vì mục tiêu của khuyên bảo này là bao gồm tất cả những cách rất có thể có, nhằm tính tổng những giá trị thỏa một vài điều kiện, họ sẽ bàn luận các lấy ví dụ công thức đối với tất cả hai hàm – SUMIFS với SUMIF với khá nhiều điều kiện. Để thực hiện chúng một cách bao gồm xác, bạn phải hiểu rõ hai hàm này có điểm gì bình thường và phương thức chúng khác biệt như nuốm nào.

Mặc cho dù nét tầm thường thì khá cụ thể – tương đương về điểm đến lựa chọn cho công dụng cuối thuộc và những tham số – trong lúc sự biệt lập dù ko rõ rang mà lại vẫn rất cần thiết.

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

Trong các hàm Excel SUMIF cùng SUMIFS, máy tự các đối số là khác nhau. Thay thể, sum_range là tham số thứ nhất trong SUMIFS, nhưng lại lại đứng số 3 trong bí quyết SUMIF.

Xem thêm: Các Trang Web Đen Nổi Tiếng, Top Website Truy Cập Nhiều Nhất Việt Nam 2021

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

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

2. Size của đối số sum_range với criteria_range

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

Ví dụ, bí quyết = SUMIF (A2: A9, F1, C2: C18) sẽ trả lại công dụng đúng vì Excel chỉ xem ô phía trên bên trái trong đối số sum_range (C2 trong ví dụ này là đúng) và sau đó bao gồm số cột cùng hàng 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! vị 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 xong xuôi lý thuyết rồi, bắt buộc mục sau bọn họ sẽ gửi 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 người đi làm

Làm nỗ lực nào để sử dụng SUMIFS trong Excel – lấy ví dụ như công thức

Vừa nãy, họ đã bàn luận về một công thức SUMIFS đơn giản và dễ dàng với hai điều kiện văn bản. Với phương pháp tương từ như thế, bạn 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à những 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, đưa sử, bạn có nhu cầu tính tổng tất cả các lượng hàng đã có được Mike cung cấp với số lượng. Tự 200 trở lên. Để làm điều này, bạn thực hiện toán tử so sánh “lớn hơn hoặc bằng” (> =) trong những điều kiện 2 và nhận được phương pháp 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, những biểu thức lôgic với các toán tử đối chiếu phải luôn luôn được để trong vệt nháy kép (“”).

Chúng tôi đã đề cập cụ thể tất cả các toán tử so sánh hoàn toàn có thể khi thảo luận 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ề quý hiếm tổng của tất cả các giá chỉ trị trong các ô C2: C9 mà lớn hơn hoặc bởi 200 và nhỏ tuổ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àyTrong ngôi trường hợp bạn có nhu cầu tính tổng các giá trị với nhiều điều kiện dựa trên ngày hiện nay tại, hãy sử 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 mức vốn trong cột D nếu ngày khớp ứng trong cột C lâm vào tình thế khoảng thời gian 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, 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 những giá trị mà có ô trống khớp ứng (hoàn toàn không đựng dũ liệu – ko công thức, cùng chuỗi gồm 0 kí tự)=SUMIFS(C2:C10, A2:A10, “=”, B2:B10, “=”)

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 là ô trọn vẹn trống.

“”Tính tổng các giá trị khớp ứng với những ô white “nhận định trực quan”, bao hàm các quý giá chứa các chuỗi rỗng được trả về bởi một trong những hàm Excel không giống (ví dụ: ô tất cả 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, tuy thế có bao hàm các chuỗi trống.

Những ô ko trống“”Tính tổng các giá trị mà có các giá trị tương ứng là các ô không trống, với có bao hà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ó trong cột A và B ko là ô trống, có bao hàm các ô với chuỗi trống.

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

Và bây giờ, hãy cùng xem cách chúng ta có thể sử dụng cách làm SUMIFS với đk “trống” với “không trống” trên tài liệu thực như vậy nào:

Giải pháp là áp dụng công thức SUMIFS với 2 điều kiện:

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


Ví dụ 4: bí quyết viết đk 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à loại 3 (Ngày)Bước 1: Phân tích đề bài

Đề bài xích yêu mong tính tổng theo 2 điều khiếu nại là Ngày cùng Mã, do đó ta ko thể áp dụng hàm SUMIF mà phải áp 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 đề nghị tính chưa hẳn cố định nhưng tùy biến, viết 1 hàm SUMIFS trên B4 rồi copy hàm đó sang trọng các ô khác để tínhĐiều kiện Mã cùng Ngày rất 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 con số trong vùng bảng tài 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 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 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 ô B3

Do đó bí quyết tại ô B4 rất có thể viết như sau: