Sumif ( hàm excel thông dụng: hàm sumif (hàm sumif), sumif (hàm sumif)

Trong bài viết này, Học Excel Online sẽ phân tích và lý giải hàm SUMIF và hỗ trợ một số lấy ví dụ công sản phẩm công nghệ về hàm  SUMIF cho tài liệu dạng số, văn bản, tháng ngày và phần đông kí từ đại diện.

Bạn đang xem: Hàm excel thông dụng: hàm sumif

Nếu nhiều người đang phải đối mặt với trọng trách tính tổng các dữ liệu thỏa điều kiện trong Excel, thì hàm SUMIF là các thứ mà bạn cần. Trả lời này sẽ lý giải ngắn gọn về cú pháp của hàm và cách thực hiện chung, với tiếp đó các bạn sẽ bổ sung thêm kỹ năng trong việc thực hành với một số ví dụ cách làm của hàm SUMIF.

Làm cầm nào để thực hiện Hàm SUMIF trong Excel – công thức minh họa
Công thức SUMIF cùng với kí từ đại diện
Tổng những số lớn số 1 hoặc nhỏ nhất trong một dải:Những thắc mắc thường gặp hoặc “Tại sao cách làm SUMIF của tớ không hoạt động?”
SUMIF trong Excel – cú pháp và giải pháp sử dụngHàm SUMIF, nói một cách khác là tính tổng có đk của Excel, được áp dụng để thêm những ô, dựa vào một đk hoặc tiêu chuẩn nhất định.

Nếu các bạn đã đọc chỉ dẫn về hàm COUNTIF trên trang blog này, thì bạn sẽ không gặp ngẫu nhiên khó khăn làm sao khi hiểu hàm SUMIF, vày cú pháp với cách áp dụng của nó cũng tương tự. Vì chưng đó, cú pháp của hàm SUMIF như sau:

SUMIF (range, criteria, )

Như bạn thấy, hàm SUMIF bao gồm 3 đối số – 2 đối số đầu là bắt buộc, còn đối số sản phẩm 3 là tùy chọn.

Lưu ý: Hãy chú ý rằng bất kỳ điều khiếu nại dạng văn bạn dạng hoặc điều kiện có chứa những ký hiệu toán học thì đều bắt buộc được đặt trong dấu nháy kép (“”). Đối với các điều kiện số, dấu nháy kép là ko bắt buộc.

sum_range – các ô dùng để làm tính tổng, nếu điều kiện được đáp ứng. Đối số này là tùy các bạn chọn, và bạn chỉ việc sử dụng nó nếu bạn có nhu cầu tính tổng các ô không giống (mà vẫn được có mang trong đối số range). Ví như đối số sum_range bị quăng quật qua, khi đó Excel và tính tổng những ô tương tự như mà các tiêu chí được đặt ra (nghĩa là bọn chúng đã được chỉ định và hướng dẫn trong đối số range).

Để minh họa cú pháp hàm SUMIF một biện pháp dễ hiểu, hãy chăm chú ví dụ sau đây. Giả sử chúng ta có một danh sách các thành phầm trong cột A và số lượng tương ứng vào kho trong cột C. Vụ việc đưa ra là bạn muốn biết tổng của tất cả các lượng sản phẩm tồn kho liên quan đến một sản phẩm nhất định, ví dụ: bananas.

Bây giờ, hãy khẳng định các đối số cho cách làm SUMIF của bọn chúng ta:

range-A2: A8criteria – “bananas”sum_range – C2: C8

Gộp những đối số lại và chúng ta nhận được công thức: = SUMIF (A2: A8, “bananas”, C2: C8)

Công thức lấy ví dụ như này thể hiện bí quyết sử dụng đơn giản và dễ dàng của hàm SUMIF vào excel cùng với các tiêu chí dạng văn bản. Thay vì chưng văn bản, bạn có thể đưa vào trong 1 số, ngày hoặc một ô được tham chiếu mang đến trong tiêu chuẩn bạn gửi ra. Ví dụ, bạn có thể viết lại phương pháp trên để nó tham chiếu mang đến ô bao gồm chứa thương hiệu của thành phầm được tính tổng:

= SUMIF (A2: A8, F1, C2: C8)

Chú thích: tham số sum_range thực sự chỉ khẳng định ô trên cùng phía bên trái của dải được tính tổng. Quanh vùng còn lại thì lại được xác định bởi kích thước của đối số range.

Trong thực tế, điều này tức là đối số sum_range không tốt nhất thiết phải có cùng size với đối số dải, tức là nó rất có thể khác nhau về con số hàng với cột. Mặc dù nhiên, ô thứ nhất (tức là ô bên trên cùng bên trái) của dải tính tổng phải luôn luôn nghỉ ngơi đúng cột. Ví dụ, trong công thức SUMIF ngơi nghỉ trên, chúng ta có thể lấy ô C2, hoặc C2: C4, hay thậm chí còn là C2: C100 làm cho đối số sum_range, và kết quả vẫn vẫn như cũ. Tuy nhiên, phía đi tốt nhất vẫn là cung ứng range với sum_range có thuộc kích thước.

Làm núm nào để thực hiện Hàm SUMIF vào Excel – phương pháp minh họa

Hy vọng rằng, lấy một ví dụ trên đã khiến cho bạn hiểu cơ phiên bản được cách hoạt động vui chơi của hàm SUMIF. Bên dưới đây bạn sẽ tìm thấy một vài công thức cho biết rằng làm chũm nào để sử dụng hàm SUMIF vào Excel với các tiêu chí khác biệt và loạt tài liệu khác nhau.

SUMIF với dấu béo hơn, nhỏ hơn hoặc bằng:

Hãy nhìn vào một vài công thức hàm SUMIF mà chúng ta cũng có thể sử dụng nhằm thêm vào các giá trị khủng hơn, nhỏ hơn hoặc bởi một quý giá nhất định.

Lưu ý: trong các công thức hàm SUMIF, một dấu đối chiếu theo sau bởi một số trong những hoặc một văn bản phải luôn luôn được để trong lốt nháy kép (“”).

Điều kiện	Toán tử	Công thức ví dụ	Giải thích
Tính tổng nếu béo hơn	>	=SUMIF(A2:A10, “>5”)	Tính tổng các số lớn hơn 5 vào vùng A2:A10
Tính tổng nếu nhỏ tuổi hơn		=SUMIF(A2:A10, “”&D1, B2:B10)	Tính tổng những ô trong cột B, nếu các ô tương ứng, cùng cái trong cột A có mức giá trị khác cực hiếm ở D1
Tính tổng nếu lớn hơn hoặc bằng	>=	=SUMIF(A2:A10, “>=5”)	Tính tổng các số lớn hơn hoặc bằng 5 vào vùng A2:A10
Tính tổng nếu bé dại hơn hoặc bằng

Hàm SUMIF tính tổng theo điều kiện so sánh với chuỗi

Hãy để ý rằng bạn sẽ cần những công thức hàm SUMIF khác biệt để đối chiếu chính xác và từng phần, như được trình diễn trong bảng dưới đây.

Tiêu chí	Công thức ví dụ	Mô tả
Tính tổng trường hợp bằng	Đối chiếu hoàn toàn:=SUMIF(A2:A8, “bananas”, C2:C8)	Giá trị tính tổng trong những ô C2:C8 nếu ô tương xứng trong cột A chứa đúng đắn từ “bananas” cùng không dư một từ hay kí trường đoản cú nào. Những ô cất “green bananas”, “bananas green” or “bananas!” không bao gồm trong trường vừa lòng này.
Đối chiếu từng phần:=SUMIF(A2:A8, “*bananas*”, C2:C8)	Giá trị tính tổng trong số ô C2:C8 trường hợp ô tương ứng trong cột A đựng từ “bananas”, đứng hòa bình hoặc đi kèm theo với rất nhiều từ khác”. Những ô đựng “green bananas”, “bananas green” or “bananas!” sẽ tiến hành tính trong trường hợp này.
Tính tổng nếu khác	Đối chiếu hoàn toàn:=SUMIF(A2:A8, “bananas”, C2:C8)	Giá trị tính tổng trong các ô C2:C8 giả dụ ô tương xứng trong cột A chứa bất kỳ giá trị nào nhưng khác “bananas”. Trường hợp ô chứa “bananas” có thêm một số từ xuất xắc kí trường đoản cú khác, ví như “yellow bananas” hoặc “bananas yellow”, thì chúng sẽ tiến hành tính tổng.
Đối chiếu từng phần:=SUMIF(A2:A8, “*bananas*”, C2:C8)	Giá trị tính tổng trong số ô C2:C8 nếu ô tương xứng trong cột A chứa bất kỳ giá trị nào cơ mà khác “bananas”. Ví như ô cất “bananas” có thêm một số từ bỏ khác. Các ô như “yellow bananas” or “bananas yellow” sẽ không còn thỏa tiêu chuẩn này.

Để biết thêm tin tức về quý giá khớp 1 phần, vui mắt xem ví dụ về hàm SUMIF trong excel với các kí tự đại diện.

Và bây giờ, hãy xem công thức đúng đắn “Tính tổng trường hợp khác” qua những thao tác. Như được minh họa trong ảnh chụp màn hình hiển thị bên dưới, nó cho biết thêm lượng tồn kho của tất cả các sản phẩm khác ko kể “goldfinger bananas”:

= SUMIF (A2: A7, " goldfinger bananas", C2: C7) 

Lưu ý: giống như như phần đông các hàm Excel khác, SUMIF không riêng biệt chữ hoa chữ thường, có nghĩa là dù các bạn nhập “ Bananas” cùng “Sử dụng dấu so sánh với tham chiếu ô

Nếu bạn có nhu cầu tiến xa rộng và ước ao có một công thức hàm SUMIF ít nhiều hơn, chúng ta có thể thay nắm giá trị số hoặc văn bản trong tiêu chí bằng một ô tham chiếu, như sau:

= SUMIF (A2: A8, "" & F1, C2: C8)

Trong trường hòa hợp này, bạn sẽ không phải chuyển đổi công thức nhằm tính tổng có đk dựa trên các tiêu chuẩn khác – nhưng bạn chỉ việc nhập một giá trị mới vào một trong những ô được tham chiếu.

Chú thích. Khi chúng ta sử dụng một biểu thức logic với một tham chiếu ô, bạn phải thực hiện dấu nháy kép (“”) để bắt đầu một chuỗi văn bạn dạng và dấu và (&) nhằm nối/kết thúc chuỗi, ví dụ như “” và F1.

Dấu “bằng” (=) rất có thể được bỏ lỡ trước một tham chiếu ô, cho nên cả hai cách làm dưới đây là mang tính tương tự và chủ yếu xác:

Công thức 1: = SUMIF (A2: A8, "=" và F1, C2: C8)

Công thức 2: = SUMIF (A2: A8, F1, C2: C8)

Công thức SUMIF với kí trường đoản cú đại diện

Nếu chúng ta mục đích của chúng ta là việc tính tổng có điều kiện những ô dựa trên tiêu chí “văn bản” và bạn muốn tính tổng bằng cách đối chiếu từng phần, thì bạn cần phải sử dụng các kí tự thay mặt trong công thức SUMIF của bạn.

Hãy coi xét các kí tự đại diện sau đây:

Dấu hoa thị (*) – đại diện thay mặt cho nhiều kí tự
Dấu hỏi (?) – đại diện cho một kí từ bỏ duy nhất ở một vị trí rứa thể

Ví dụ 1. Những giá trị tính tổng dựa trên so sánh từng phần:

Giả sử, bạn có nhu cầu tính toàn bô tiền tương quan đến tất cả các loại chuối (bananas). Thì các công thức SUMIF sau đã chạy như sau:

= SUMIF (A2: A8, "bananas *", C2: C8)– tiêu chí bao gồm các văn bản kết thúc với dấu hoa thị (*).

= SUMIF (A2: A8, "*" và F1 và "*", C2: C8)– các tiêu chí bao gồm 1 tham chiếu ô đương nhiên dấu hoa thị, vui lòng xem xét về việc thực hiện dấu cùng (&) trước và sau một tham chiếu ô nhằm nối cùng với chuỗi.

Nếu chúng ta chỉ muốn tính tổng đa số ô ban đầu hoặc hoàn thành với một văn bản nhất định, thì bạn chỉ cần thêm một vệt * trước hoặc sau văn bản:

= SUMIF (A2: A8, "bananas*", C2: C8) – tổng mức vốn trong C2: C8 ví như một ô tương xứng trong cột A ban đầu bằng trường đoản cú “bananas”.

= SUMIF (A2: A8, "*bananas", C2: C8) – tổng mức vốn trong C2: C8 nếu một ô tương ứng trong cột A chấm dứt bằng từ bỏ “bananas”.

Ví dụ 2. Tổng mức vốn với số kí tự độc nhất định

Trong trường hợp bạn muốn tính tổng một vài giá trị nếu cực hiếm này có đúng đắn 6 chữ cái, thì bạn sẽ sử dụng công thức sau:

= SUMIF (A2: A8, "??????", C2: C8)

Ví dụ 3. Tính tổng các ô có mức giá trị tương xứng với quý giá văn bản

Nếu bảng tính của người tiêu dùng chứa những loại dữ liệu không giống nhau và chúng ta chỉ mong mỏi tổng những ô tương ứng với cực hiếm văn bản, thì những công thức SUMIF sau sẽ hữu dụng cho bạn:

= SUMIF (A2: A8, "? *", C2: C8) – cùng giá trị trong số ô C2: C8 nếu ô khớp ứng trong cột A chứa tối thiểu 1 kí tự.

= SUMIF (A2: A8, "*", C2: C8) – bao gồm các ô có vẻ như là rỗng, chứa các chuỗi có độ dài bởi 0 (là hiệu quả của những công thức khác), ví dụ: = “”.

Cả hai công thức trên đều vứt qua các giá trị không hẳn là văn phiên bản như những lỗi, những phép toán luận, các chữ số với ngày tháng.

Ví dụ 4. áp dụng * hoặc ? tựa như các kí trường đoản cú bình thường

Nếu bạn có nhu cầu * hoặc ? được xem như là một chữ chứ không phải là 1 trong những kí trường đoản cú đại diện, thì nên sử dụng dấu vấp ngã trước kia (~). Ví dụ: phương pháp SUMIF sau đã thêm những giá trị trong số ô C2: C8 trường hợp một ô trong cột A phía trong cùng một hàng mà đựng một vệt chấm hỏi:

= SUMIF (A2: A8, "~?", C2: C8)

Tổng những số lớn số 1 hoặc nhỏ tuổi nhất vào một dải:

Để tính tổng các số lớn nhất hoặc bé dại nhất trong dải, hãy sử dụng hàm SUM cùng với hàm LARGE hoặc SMALL.

Ví dụ 1. Thêm một vài số lớn nhất / nhỏ tuổi nhất

Nếu bạn có nhu cầu tính tổng chỉ một số trong những ít, ví dụ như 5 số, thì chúng ta có thể gõ chúng trực tiếp trong công thức, như sau:

= SUM (LARGE (B1: B10, 1,2,3,4,5)) – tổng 5 số to nhất

= SUM (SMALL (B1: B10, 1,2,3,4,5)) – tổng 5 số nhỏ tuổi nhất

Chú thích. Nếu tất cả 2 hoặc nhiều hơn số được buộc mang lại vị trí cuối cùng, chỉ gồm số trước tiên xuất hiện sẽ tiến hành tính. Như chúng ta thấy, số 9 đồ vật hai ko được tiếp tế trong ví dụ như trên.

Ví dụ 2. Thêm vào các giá trị lớn/nhỏ

Nếu bạn muốn thêm những số, thay vì chưng liệt kê tất cả trong công thức, bạn cũng có thể tổ chức những hàm ROW với hàm INDIRECT trong bí quyết SUM của bạn. Vào hàm INDIRECT, hãy áp dụng số hàng thay mặt đại diện cho số giá bán trị bạn có nhu cầu thêm vào. Ví dụ, những công thức sau đây tính tổng 15 số lớn nhất và nhỏ nhất, theo lần lượt là:

= SUM (LARGE (B1: B50, ROW (INDIRECT ("1:15"))))

= SUM (SMALL (B1: B50, ROW (INDIRECT ("1:15"))))

Vì đây là các công thức mảng, nên các bạn hãy nhớ nhập chúng bằng cách nhấn tổ hợp phím Ctrl + Shift + Enter.

Ví dụ 3. Tính tổng một phát triển thành số trong những giá trị lớn số 1 / bé dại nhất

Nếu các bạn không muốn biến đổi công thức mỗi khi bạn muốn tính tổng một số ô không giống nhau, bạn cũng có thể đặt số kia trong một ô nào đó thay vì nhập nó vào công thức. Với sau đó, chúng ta có thể sử dụng hàm ROW cùng INDIRECT như đã trình diễn ở trên cùng tham chiếu một ô cất biến, như trong trường hòa hợp này là ô E1:

= SUM (LARGE (B1: B50, ROW (INDIRECT ("1:" & E1)))) – tính tổng các giá trị bậc nhất mà bao gồm chứa 1 ô cụ đổi

= SUM (SMALL (B1: B50, ROW (INDIRECT ("1:" và E1)))) – tính tổng các giá trị bé dại nhất mà tất cả chứa 1 ô gắng đổi

Hãy lưu giữ rằng, đó là các bí quyết mảng, do vậy các bạn phải dấn Ctrl + Shift + Enter để hoàn thành chúng.

Làm nạm nào để tính tổng những ô tương ứng với các ô trống

Nếu “trống” thì có nghĩa là các ô đó trọn vẹn không tất cả chứa gì – không tồn tại công thức, không có chuỗi chiều dài bởi 0 được trả lại bởi một trong những hàm Excel khác, lúc đó bạn cũng có thể sử dụng “=” làm cho tiêu chí, như trong bí quyết SUMIF sau:

= SUMIF (A2: A10, "=", C2: C10)

Nếu “trống” bao hàm các chuỗi độ dài bởi không (ví dụ: ô bao gồm công thức như = “”), thì bạn có thể sử dụng “” có tác dụng tiêu chí:

= SUMIF (A2: A10, "", C2: C10)

Cả hai phương pháp trên đều nhận xét các ô vào cột A với nếu có bất kỳ ô trống như thế nào được search thấy, các giá trị khớp ứng từ cột C sẽ được thêm vào.

Làm nắm nào nhằm tính tổng những ô tương xứng với các ô không trống

Để chế tạo loại cách làm “nếu ô không trống thì tính tổng”, hãy sử dụng “” làm tiêu chí. Thao tác làm việc này vẫn cộng tất cả các ô chứa ngẫu nhiên thứ gì trong đó, bao hàm cả những chuỗi có độ dài bằng không.

Ví dụ: đây là cách chúng ta có thể tính tổng doanh số bán sản phẩm cho tất cả các quần thể vực, tức là trong đó cột B không trống:

=SUMIF(B2:B10, “”, C2:D10)

Ví dụ rõ ràng về phương pháp dùng hàm sumif 4

Cách áp dụng Hàm SUMIF với ngày tháng

Nói chung, bạn sử dụng hàm SUMIF nhằm tính tổng một cách gồm điều kiện những giá trị dựa vào ngày cũng tương tự cách bạn sử dụng văn phiên bản và các tiêu chí số.

Nếu bạn muốn tính tổng các giá trị nếu những giá trị tương xứng lớn hơn, nhỏ dại hơn hoặc bởi so cùng với ngày bạn chỉ định, thì hãy sử dụng dấu đối chiếu mà chúng tôi đã bàn bạc từ những bài xích trước. Cùng dưới đó là một vài ba ví dụ cách làm hàm SUMIF:

Trong trường hợp bạn có nhu cầu tính tổng những giá trị dựa trên ngày hiện tại thời, thì bạn phải sử dụng hàm SUMIF kết phù hợp với hàm TODAY (), như sau:

Tiêu chí	Công thức ví dụ
Tính tổng các giá trị trong số ô dựa vào ngày hiện tại thời	=SUMIF(B2:B9, TODAY(), C2:C9)
Tính tổng những giá trị ô mà bên trong quá khứ, tức là trước ngày hiện nay thời.	=SUMIF(B2:B9, “”&TODAY(), C2:C9)
Tính tổng những giá trị ô nếu các ngày tương ứng đều được xảy ra trong một tuần (có tức thị ngày giờ đây + 7 ngày nữa)	=SUMIF(B2:B9, “=”&TODAY()+7, C2:C9)

Hình sau đây minh hoạ cách chúng ta cũng có thể sử dụng công thức sau cuối để kiếm tìm tổng số tiền của tất cả các thành phầm được đưa đi vào một tuần.

Cách tính tổng những giá trị ứng với vào một quãng thời gian nhất định

Câu hỏi tiếp sau đây được hỏi liên tiếp trên những diễn bọn và blog của Excel – “Làm phương pháp nào để tính tổng các giá trị ứng với thời gian nằm thân hai ngày?”

Câu vấn đáp là áp dụng một sự kết hợp, hoặc đúng chuẩn hơn, là sự biệt lập giữa 2 công thức SUMIF. Vào Excel 2007 về bên bây giờ, bạn cũng có thể sử dụng hàm SUMIFS có thể chấp nhận được nhiều tiêu chí, thậm chí là 1 trong những lựa chọn tốt hơn. Shop chúng tôi sẽ nói tới vế sau trong chủ thể của nội dung bài viết tiếp theo, còn dưới đây sẽ là lấy một ví dụ về công thức SUMIF:

Công thức này hoàn toàn có thể có vẻ hơi nặng nề khăn nếu như khách hàng chỉ lướt qua, cơ mà khi ví như đi sâu vào nó, thì bạn sẽ thấy nó rất đơn giản. Hàm SUMIF đầu tiên đã thêm toàn bộ các ô vào C2: C9, trường hợp ô tương xứng trong cột B to hơn hoặc bởi ngày ban đầu (trong lấy một ví dụ này là ngày một tháng 10). Tiếp đó, bạn chỉ việc phải nhiều loại đi ngẫu nhiên giá trị nào xẩy ra vào sau ngày hoàn thành (Oct-10 hay ngày 31/10) – là hầu như giá trị được trả về do hàm SUMIF lắp thêm hai.

Làm cụ nào để tính tổng các giá trị vào một vài cột

Để hiểu vụ việc hơn, hãy lưu ý ví dụ sau đây: trả sử bạn có một bảng nắm tắt doanh thu hàng tháng và nó được thu thập từ một số report cho từng khu vực vực, trong các số đó có một vài hồ sơ đến cùng một thành phầm ở trên:

Vì vậy, làm nạm nào để bạn tìm thấy tổng thể apples (táo) được chào bán ở tất cả các tiểu bang trong bố tháng qua?

Như chúng ta đã biết, form size của sum_range được khẳng định bởi form size của tham số range. Đó là tại sao tại sao chúng ta không thể áp dụng công thức như = SUMIF (A2: A9, “apples”, C2: E9) cũng chính vì nó đang thêm những giá trị tương xứng với “Apples” cơ mà chỉ nghỉ ngơi trong cột C. Với đây chưa phải là phần đa gì bọn họ đang tìm kiếm.

Giải pháp hợp lí và dễ dàng nhất cho thấy thêm chính nó là tạo nên một cột nhằm trợ góp tính từng tổng số cho từng hàng và sau đó cột này sẽ được tham chiếu trong tiêu chí sum_range.

Đi trước cùng đặt phương pháp SUM dễ dàng và đơn giản trong ô F2, kế tiếp điền vào cột F: = SUM (C2: E2)

Sau đó, chúng ta có thể viết một cách làm SUMIF đơn giản và dễ dàng như sau:

= SUMIF (A2: A9, "apples", F2: F9)

hoặc là

= SUMIF (A2: A9, H1, F2: F9)

Trong những công thức trên, sum_range có cùng form size với dải, tức là 1 cột và 8 hàng, và vì chưng đó tác dụng mà nó trả lại sẽ chính xác:

Nếu bạn có nhu cầu làm mà không tồn tại một cột trợ giúp, sau đó bạn cũng có thể viết một công thức SUMIF riêng cho từng cột bạn muốn tính tổng, và tiếp đến thêm các tác dụng trả về cùng với hàm SUM:

= SUM (SUMIF (A2: A9, I1, C2: C9), SUMIF (A2: A9, I1, D2: D9), SUMIF (A2: A9, I1, E2: E9))

Một biện pháp khác là áp dụng một cách làm mảng phức hợp hơn (nhưng cũng hãy nhờ rằng nhấn Ctrl + Shift + Enter):

= SUM ((C2: C9 + D2: D9 + E2: E9) * (- (A2: A9 = I1)))}

Cả hai cách làm trên sẽ gửi ra tác dụng là 2070 trong trường đúng theo trên.

Những câu hỏi thường gặp mặt hoặc “Tại sao bí quyết SUMIF của tôi không hoạt động?”

Có thể bao gồm vài lý do khiến cho Hàm SUMIF không hoạt động trong tình huống của bạn. Đôi khi phương pháp của bạn sẽ không trả lại hầu hết gì bạn ao ước đợi chỉ vì chưng kiểu dữ liệu trong một ô hoặc trong một vài đối số không phù hợp với hàm SUMIF. Bởi vì đó, dưới đó là một list những vấn đề cần kiểm tra:

1. Các tham số của range và sum_range bắt buộc là những dải, chứ không phải mảng:

Các tham số trước tiên (range) cùng thứ ba (sum_range) trong công thức SUMIF của người sử dụng phải vẫn là một tham chiếu dải, như A1: A10. Ví như bạn bất chấp mà bỏ lỡ điều này, lấy ví dụ là trường đúng theo tham chiếu một mảng như 1,2,3, thì Excel với trả lại cùng với một thông báo lỗi.

Công thức đúng: = SUMIF (A1: A3, "flower", C1: C3)

Công thức sai: = SUMIF (1,2,3, "flower", C1: C3)

2. Làm rứa nào để tính tổng vốn từ những trang tính hoặc bảng tính khác:

Củng tương đương như phần lớn các hàm Excel khác, SUMIF có thể tham chiếu đến những trang tính với bảng tính khác, miễn là chúng vẫn đang mở.

Ví dụ: công thức dưới đây sẽ tính tổng những giá trị trong các ô F2: F9 vào Sheet 1 (trang tính 1) của Book 1 (bảng tính 1) nếu bao gồm một ô khớp ứng trong cột A và tất cả trang tính đó cũng có thể có chứa “apples”:

= SUMIF ( Sheet1! $A$2:$A$9, "apples", Sheet1! $F$2:$F$9)

Tuy nhiên, công thức này sẽ không hoạt động ngay lúc Book 1 bị đóng lại. Điều này xẩy ra vì những dải được tham chiếu bởi các công thức SUMIF trong bảng tính đã đóng, vậy đề xuất nó sẽ không được tham chiếu đến những mảng. Khía cạnh khác, cũng do không tồn tại mảng nào được được cho phép trong range với đối số sum_range, đề nghị sau đó, cách làm SUMIF đã trả về một thông tin lỗi #VALUE!.

3. Hãy chắc chắn range với sum_range có cùng kích cỡ:

Như đã chú ý ở phần đầu của gợi ý này, trong các phiên phiên bản hiện đại của Microsoft Excel, các tham số range với sum_range không tuyệt nhất thiết phải bằng nhau. Tự phiên bản Excel 2000 quay trở lại trước, range cùng sum_range có kích cỡ không đồng đều có thể gây ra vấn đề. Tuy nhiên, ngay cả trong số phiên phiên bản mới duy nhất của Excel 2010 và 2013, những công thức phức tạp SUMIF cơ mà ở đó, nếu sum_range có ít mặt hàng và/hoặc cột hơn range, thì sẽ không hợp lệ. Đó là vì sao tại sao nó được xem là một minh họa giỏi để fan dùng để ý rằng phải luôn luôn duy trì đối số range cùng sum_range cùng size và hình dạng.

4. Làm cố kỉnh nào để bí quyết SUMIF của bạn vận động nhanh hơn:

Nếu các bạn đã điền vào bảng tính với những công thức phức hợp SUMIF làm chậm chạp Excel, hãy xem thử nội dung bài viết này và mày mò Làm thay nào để chúng vận động nhanh hơn. Nội dung bài viết đã được viết cách đây khá lâu, vì chưng vậy đừng ngạc nhiên bởi thời gian tính toán. Các ví dụ về Excel được trình diễn trong lý giải này chỉ tương quan đến một vài cách thực hiện cơ bạn dạng của hàm này. Trong bài viết tiếp theo, shop chúng tôi sẽ gửi ra các công thức tiên tiến nhằm mục tiêu khai thác sức khỏe thực của SUMIF và SUMIFS và có thể chấp nhận được bạn tính tổng theo không ít tiêu chí.

– – – – –

Ngoài ra để vận dụng Excel vào công việc một cách hiệu quả thì bạn còn nên sử dụng tốt các hàm, các công nạm khác của Excel.

Một số hàm cơ phiên bản thường gặp như:

SUMIF, SUMIFS để tính tổng theo 1 điểm kiện, những điều kiện
COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, các điều kiện
Các hàm xử lý tài liệu dạng chuỗi, dạng ngày tháng, dạng số…Các hàm dò tra cứu tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ hay được dùng như:

Định dạng theo đk với Conditional formatting
Thiết lập điều kiện nhập tài liệu với Data Validation
Cách đặt Name và sử dụng Name trong công thức
Lập report với Pivot Table…

Rất nhiều kiến thức và kỹ năng phải không nào? cục bộ những kỹ năng và kiến thức này các bạn đều rất có thể học được trong khóa học EX101 – Excel tự cơ phiên bản tới chuyên viên của học tập Excel Online. Đây là khóa học giúp cho bạn hệ thống kỹ năng và kiến thức một bí quyết đầy đủ, đưa ra tiết. Hơn nữa không thể có giới hạn về thời gian học tập nên chúng ta có thể thoải mái học bất kể lúc nào, dễ dãi tra cứu vớt lại kiến thức khi cần. Hiện thời hệ thống đang xuất hiện ưu đãi vô cùng lớn cho mình khi đk tham gia khóa học.

Hàm SUMIF có tác dụng tính tổng những giá trị bằng lòng được đầy đủ điều kiện rõ ràng mà người tiêu dùng mong muốn. Trong bài viết này wu.edu.vn vẫn hướng dẫn chúng ta cách viết hàm SUMIF thông qua 1 vài ví dụ nuốm thể. Biện pháp sử dụng các điều khiếu nại dạng số, dạng ngày, dạng cam kết tự thay mặt trong hàm SUMIF. Cùng khám phá ngay nhé.

Cú pháp hàm SUMIF và biện pháp sử dụngtrong Excel

SUMIF được áp dụng để tính tổng các giá trị thỏa mãn nhu cầu điều kiện cố định (SUMIF tức là SUM + IF). Khi viết đk trong hàm thì bạn có thể sử dụng kết hợp các vết toán tử (>, =, ) hoặc các ký tự quan trọng (dấu *, lốt ?) để khẳng định điều kiện theo một khoảng tầm (chứ không phải 1 giá trị gắng thể).

Cú pháp của hàm như sau:

SUMIF(range; criteria; )

Như bạn thấy SUMIF tất cả 3 đối số – 2 đối số trước tiên là buộc phải và đối số trang bị 3 là tùy chọn.

Range – đấy là vùng đựng điều kiện. Vùng này thường nằm ở vị trí bảng dữ liệu, lấy ví dụ như Sheet1!A1:A10 (vùng A1:A10 trong Sheet1 - là Sheet đựng bảng dữ liệu)

Criteria –điều kiện làm tiêu chuẩn tính tổng. Điều khiếu nại thường nằm ở bảng báo cáo, ví dụ Sheet2!A5 (vùng A5 trong Sheet2 - là Sheet bạn muốn đặt hiệu quả tính toán, xuất xắc là bảng báo cáo)

Sum_range – vùng tính tổng. Vùng này thường nằm tại vị trí bảng tài liệu và gồm độ lớn tương ứng với vùng chứa điều kiện, lấy một ví dụ Sheet1!D1:D10 (vùng D1:D10 vào Sheet1 - là Sheet cất bảng dữ liệu).

Hàm và tính tổng đều dòng cất giá trị thỏa mãn nhu cầu tương ứng cùng với vùng đựng điều kiện. Tham số này sẽ không bắt buộc, nếu khách hàng bỏ qua thông số này, Excel và tính tổng bên trên vùng chứa đk (chỉ áp dụng khi vùng đựng điều kiện bao hàm toàn những giá trị là số).

Để hiểu rõ hơn về hàm này, hãy xem xét ví dụ hàm Sumif đơn giản sau:

Giả sử bạn có một danh sách các sản phẩm trong cột A cùng số tiền tương xứng trong cột C. Ao ước biết tổng của tất cả các khoản tương quan đến một thành phầm nhất định, ví dụ như Táo.

Bây giờ, hãy xác minh các đối số cho cách làm SUMIF:

range: A2:A8criteria: “Táo”sum_range: C2:C8

Lúc này bí quyết trên sẽ là:

=SUMIF(A2:A8; ”Táo”; C2:C8)

Trong ví dụ trên họ nhập thẳng chữ apple vào vào hàm tại địa chỉ điều kiện. Với biện pháp nhập trực tiếp vậy nên thì chữ táo apple phải được đặt trong cặp vết nháy kép là "Táo", bởi vì đây là ký tự dạng văn bản.

Có 1 cách viết khác là bạn cũng có thể chọn tham chiếu tới 1 ô cất nội dung là chữ Táo, ví dụ như đó là ô E1 thì có thể viết như sau:

=SUMIF(A2:A8; E1; C2:C8)

Một số ví dụ về những trường hợp áp dụng hàm SUMIF

SUMIF có thể sử dụng với khá nhiều dạng điều kiện khác nhau. Dưới đây là một số lấy ví dụ như về các trường hợp sử dụng hàm mà chúng ta cũng có thể gặp trong thực tế:

SUMIF cùng với dấu mập hơn, nhỏ tuổi hơn hoặc bằng

Bây giờ hãy xem xét một vài phương pháp hàm SUMIF mà bạn cũng có thể sử dụng để thêm những giá trị béo hơn, bé dại hơn hoặc bởi một quý hiếm nhất định.

Ghi chú. Trong số công thức SUMIF của Excel, một toán tử so sánh được theo sau bởi một số hoặc văn bạn dạng phải luôn được để trong vệt ngoặc kép (“”).

Tiêu chí	Dấu nối	Công thức	Miêu tả
Tính tổng nếu to hơn	>	=SUMIF(A2:A10;”>6″)	Tính tổng các giá trị lớn hơn 6 trong các ô A2:A10
Tính tổng nếu nhỏ tuổi hơn	(có thể được quăng quật qua)	=SUMIF(A2:A10;”=”&D1)hoặc =SUMIF(A2:A10;D1)	Tính tổng những giá trị trong các ô A2:A10 bằng với mức giá trị vào ô D1
Tính tổng còn nếu không bằng		=SUMIF(A2:A10;””&D1; B2:B10)	Tính tổng những giá trị trong những ô B2:B10 giả dụ một ô tương ứng trong cột A không bằng giá trị trong ô D1
Tính tổng nếu to hơn hoặc bằng	>=	=SUMIF(A2:A10;”>=6″)	Tính tổng các giá trị to hơn hoặc bởi 6 vào phạm vi A2:A10
Tính tổng nếu nhỏ hơn hoặc bằng

Hướng dẫn cách sử dụng công thức SUMIF trong EXCEL qua những ví dụ cố gắng thể

Cách áp dụng SUMIF với văn bản

Ngoài các số, hàm SUMIF được cho phép thêm các giá trị tùy ở trong vào việc một ô tương xứng trong cột khác bao gồm chứa văn bản đã mang lại hay không.

Tiêu chí	Công thức	Miêu tả
Tính tổng nếu bằng	Tham chiếu chính xác: =SUMIF(A2:A8; “Táo”;C2:C8)	Tổng giá chỉ trị trong những ô C2:C8 ví như một ô tương ứng trong cột A chứa chính xác từ “Táo” và không tồn tại từ hoặc cam kết tự làm sao khác. Các từ khóa như “Táo xanh”, “Táo đỏ” hoặc “Táo!” sẽ không còn được tính
Tính tổng giả dụ bằng	Tham chiếu một phần: =SUMIF(A2:A8; “Táo“;C2:C8)	Tổng giá chỉ trị trong số ô C2:C8 trường hợp một ô tương ứng trong cột A cất từ “Táo”, một mình hoặc phối hợp với ngẫu nhiên từ như thế nào khác. Các từ khóa như “Táo xanh”, “Táo đỏ” hoặc “Táo!” đều được xem tổng
Tổng nếu không bằng	Tham chiếu thiết yếu xác: =SUMIF(A2:A8;” Táo”;C2:C8)	Tổng giá trị trong những ô C2:C8 trường hợp một ô khớp ứng trong cột A chứa bất kỳ giá trị nào bên cạnh “Táo”. Ví như một ô cất “Táo” thuộc với một vài từ hoặc cam kết tự khác như “Táo đỏ” hoặc “Táo xanh”, các ô đó sẽ được tính tổng
Tổng còn nếu không bằng	Tham chiếu một phần: =SUMIF(A2:A8;” Táo“;C2:C8)	Tổng giá trị trong các ô C2:C8 trường hợp một ô tương xứng trong cột A không đựng từ “Táo”, một mình hoặc phối hợp với bất kỳ từ nào khác. Các từ khóa như “Táo xanh” hoặc “Táo đỏ” sẽ không được tính

Bây giờ, hãy xem cách làm “Tính tổng còn nếu như không bằng” đúng đắn trong thực tiễn sẽ ra làm sao của ví dụ dưới đây:

=SUMIF(A2:A7;”Táo xanh”;C2:C7)

Lưu ý: tương tự như phần nhiều các hàm Excel khác, hàm SUMIF không sáng tỏ chữ hoa chữ thường. Chúng ta cũng có thể viết là “táo”, “Táo” với “TÁO” hầu hết có công dụng giống nhau.

Nếu hy vọng sử dụng bí quyết hàm SUMIF một cách tổng thể hơn, bạn có thể sử dụng biện pháp viết tham chiếu tới một ô như sau:

=SUMIF(A2:A8;””&E1;C2:C8)

Chú ý:

Khi viết dạng tham chiếu kết hợp với dấu toán tử, bạn phải đặt lốt toán tử vào trong cặp dấu nháy kép ("") cùng thêm dấu & để nối với tọa độ tham chiếu.Toán tử “bằng” (=) rất có thể không cần được viết trực tiếp trong công thức mà vẫn gọi đúng, ví dụ như 2 cách viết tiếp sau đây đều như là nhau

Công thức 1: =SUMIF(A2:A8;”=”&E1;C2:C8)

Công thức 2: =SUMIF(A2:A8;E1;C2:C8)

Công thức hàm SUMIF với các ký từ bỏ đại diện

Nếu nhắm đến các ô có đk dựa trên tiêu chí “văn bản” và bạn muốn tính tổng theo từng phần thì cần sử dụng những ký tự đại diện trong công thức hàm SUMIF của mình.

Các cam kết tự đại diện thay mặt sau đây đông đảo được hỗ trợ trong Excel:

Dấu hoa thị (*) – Đại diện cho bất kỳ ký từ bỏ nào, không giới hạn số lượng cam kết tự
Dấu hỏi (?) – Đại diện đến một ký tự ở một vị trí cầm cố thể

Ví dụ 1: Tính tổng những giá trị dựa trên đối chiếu từng phần

Giả sử, bạn có nhu cầu tính tổng số tiền liên quan đến toàn bộ các nhiều loại táo. Các công thức hàm SUMIF dưới đây đều đúng:

=SUMIF(A2:A8;”táo“;C2:C8) – tiêu chí bao hàm văn bạn dạng được đính kèm trong vết hoa thị ().

=SUMIF(A2:A8;”“&E1&”*”;C2:C8) – Tiêu chí bao gồm 1 tham chiếu ô được để trong vết hoa thị.

Nếu ý muốn chỉ đếm phần nhiều ô bước đầu hoặc ngừng bằng một văn phiên bản nhất định, hãy thêm duy nhất dấu * trước hoặc sau văn bản:

=SUMIF(A2:A8;”táo“;C2:C8) – tổng vốn từ những ô C2:C8 giả dụ một ô tương ứng trong cột A bước đầu bằng tự “táo”.

=SUMIF(A2:A8;”táo”;C2:C8) – tổng giá trị từ những ô C2:C8 nếu một ô tương xứng trong cột A kết thúc bằng từ bỏ “táo”.

=SUMIF(A2:A8;”“&E1&”“;C2:C8) – Tính tổng mức vốn từ các ô C2:C8 nếu gồm từ “táo”.

Ví dụ 2. Tính tổng những ô tương xứng với những giá trị văn bản

Nếu bảng tính của công ty chứa các loại dữ liệu khác nhau và chúng ta chỉ mong muốn tính tổng các ô khớp ứng với những giá trị văn bản, các công thức SUMIF sau phần nhiều trả về và một kết quả:

=SUMIF(A2:A8;”?“;C2:C8) – Cộng các giá trị trong số ô C2:C8 trường hợp một ô tương ứng trong cột A chứa tối thiểu 1 cam kết hiệu.=SUMIF(A2:A8;”“;C2:C8) – Cộng các giá trị bao hàm cả những ô như ô trống bao gồm chứa các chuỗi bao gồm độ dài bằng không được trả về bởi một số trong những công thức khác, ví dụ như =”” .

Cả hai cách làm trên đều vứt qua những giá trị không phải là văn bạn dạng như các lỗi, các phép toán luận, các chữ số cùng ngày tháng.

Ví dụ 3. Sử dụng những ký hiệu * hay? như bình thường

Nếu bạn muốn sử dụng những ký hiệu * hoặc ? như là 1 trong những chữ mà không phải là ký kết tự đại diện thì nên áp dụng dấu xẻ trước (~). Ví dụ: bí quyết SUMIF sau đây sẽ thêm những giá trị trong số ô C2:C8 trường hợp một ô vào cột A trong cùng một hàng đựng một vết hỏi:

=SUMIF(A2:A8;”~?”;C2:C8)

Cách tính tổng những ô tương ứng với những ô trống

Nếu “blank” tức là các ô hoàn toàn không cất gì – không có công thức, không tồn tại chuỗi bao gồm độ dài bởi 0 được trả về bởi một vài hàm Excel khác, thì hãy sử dụng “=” có tác dụng tiêu chí, như trong phương pháp SUMIF sau:

=SUMIF(A2:A10;”=”;C2:C10)

Nếu “blank” bao gồm các chuỗi tất cả độ dài bởi không (ví dụ: những ô tất cả công thức như =”” ), thì thực hiện “” có tác dụng tiêu chí:

=SUMIF(A2:A10;””;C2:C10)

Cả hai cách làm trên đều review các ô vào cột A cùng nếu search thấy ngẫu nhiên ô trống nào, các giá trị tương ứng từ cột C sẽ được cộng vào.

Cách tính tổng những ô tương xứng với các ô ko trống

Nếu hy vọng tính tổng giá bán trị của các ô vào cột C lúc một ô khớp ứng trong cột A ko trống, hãy sử dụng “” làm tiêu chuẩn trong phương pháp hàm SUMIF:

=SUMIF(A2:A10;””;C2:C10)

Công thức trên tính những giá trị tương xứng với tất cả các ô ko trống, bao gồm các chuỗi gồm độ dài bằng không.

Cách thực hiện hàm SUMIF cùng với ngày, tháng, năm

Sử dụng hàm SUMIF để tính tổng những giá trị theo đk dựa trên ngày y như cách chúng ta sử dụng tiêu chuẩn văn bạn dạng và số.

Xem thêm: Những Tập Running Man Hài Hước Và Ratting Cao Nhất, Những Tập Running Man Hay Nhất Không Thể Bỏ Lỡ

Nếu mong mỏi tính tổng các giá trị tương xứng với những ngày mập hơn, nhỏ dại hơn hoặc bởi với ngày bạn chỉ định, thì hãy sử dụng những toán tử so sánh. Một vài ví dụ về bí quyết hàm SUMIF của Excel dựa vào ngày, mon như sau:

Tổng kết

Qua nội dung bài viết vừa rồi, wu.edu.vn sẽ hướng dẫn chúng ta cách làm việc với hàm SUMIF trong Excel cùng với hầu hết ví dụ ví dụ về cách áp dụng hàm này trong công việc thực tế.