Hướng dẫn hàm sum

Trong Excel, hàm SUMIF được cho phép bạn có thể tính tổng những dãy cùng với những ĐK không giống nhau giúp bạn thao tác làm việc tác dụng hơn, tiết kiệm được thời hạn. Nếu chúng ta chưa biết về hàm này thì có thể tìm hiểu thêm bài viết sau đây của oimlya.com.vn.

Bạn đang xem: Hướng dẫn hàm sum


NOTE: Tải Ngay 70+ bài tập excel cơ phiên bản cùng nâng cao

SUMIF là một trong những hàm phổ biến với được ưa dùng, nếu như khách hàng liên tục yêu cầu thao tác với Excel thì đây đang là 1 hàm cơ mà chúng ta cần hiểu rõ về cú pháp cũng tương tự cách thực hiện hàm, nó để giúp các bạn tương đối nhiều trong quá trình của chúng ta đó.

*

Với phương pháp mang đến ô D15 là : =SUMIF(C5:C14,"Nhân Viên",D5:D14)

Ta nhận được hiệu quả nhỏng sau: 2500000. Khi họ soát sổ lại tác dụng thì thấy rằng vào VD hàm Sumif này có 5 người dân có chức vụ là "Nhân viên", điều đó tổng phụ cấp cho tất cả những người kia với kết quả là 2500000 là đúng đắn.

*

2. cách sử dụng hàm SUMIF vào EXCEL

Dưới đó là một trong những cách làm tương quan đến phương pháp áp dụng hàm Excel SUMIF cùng với các điều kiện cùng những bộ tài liệu không giống nhau.

2.1 SUMIF to hơn, nhỏ hơn hoặc bằng

Dưới đây là một số phương pháp SUMIF nhưng mà bạn có thể sử dụng nhằm đối chiếu quý giá to hơn, bé dại rộng hoặc bởi với giá trị được đến trước.

Lưu ý: Lưu ý trong số bí quyết Excel SUMIF, theo sau toán thù tử đối chiếu là một số hoặc text phải luôn luôn luôn được đóng góp vào vệt ngoặc knghiền ("").

2.2 Cách áp dụng hàm SUMIF với điều kiện là text

Hàm SUMIF có thể chấp nhận được chúng ta có thể thêm các quý hiếm tùy ở trong vào ô khớp ứng trong cột không giống có đựng text hoặc không.

Lưu ý các bạn sẽ buộc phải sử dụng các bí quyết SUMIF khác nhau nhằm đối sánh chính xác hoặc đối sánh từng phần, những công thức này được liệt kê vào bảng dưới đây.

Tiếp theo thuộc xem công thức hàm Sum không tương đương hoạt động thế nào. Trong ví dụ bên dưới đây là tổng thể tiền toàn bộ các mặt hàng chưa phải là “goldfinger bananas”:

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

Lưu ý: Cũng y hệt như các hàm Excel không giống, hàm SUMIF không phân biệt chữ hoa với chữ thường, có nghĩa là dù chúng ta tất cả viết “bananas”, “Bananas” giỏi “BANANAS” thì sau cùng cũng trả lại một hiệu quả mà thôi.

2.3 Sử dụng tân oán tử so sánh cùng với tsay đắm chiếu ô

Nếu mong muốn công thức Excel SUMIF bình thường, chúng ta có thể sửa chữa giá trị số hoặc text trong số điều kiện bởi tham chiếu ô:

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

Trong ngôi trường hợp này các bạn ko yêu cầu đổi khác cách làm nhằm tính tổng mức dựa vào những tiêu chí khác, chỉ cần nhập một quý giá new vào ô được tsi chiếu.

Lưu ý: khi sử dụng một biểu thức súc tích với tmê man chiếu ô, các bạn đề nghị sử dụng dấu ngoặc kép (“”) để ban đầu chuỗi text và lốt cùng (&) nhằm nối với xong chuỗi, ví dụ “” & F1.

Không cần áp dụng tân oán tử “bằng” (=) trước một tmê say chiếu ô, vị vậy cả nhì phương pháp sau đây mọi đúng chuẩn cùng cho tác dụng tương tự nhau:

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

2.4 Công thức SUMIF cùng với cam kết từ bỏ đại diện

Nếu mục đích của khách hàng là tính tổng những ô dựa trên các ĐK “text” với mong tổng thích hợp bằng phương pháp tương quan một trong những phần, ngôi trường hợp này bạn sẽ bắt buộc áp dụng những ký kết tự đại diện trong bí quyết SUMIF.

Dưới đấy là những ký kết từ có sẵn:

- Dấu hoa thị (*) - thay mặt đại diện cho bất kỳ ký trường đoản cú nào- Dấu hỏi chấm (?) - đại diện thay mặt cho 1 ký tự nhất tại một địa chỉ vậy thể

lấy ví dụ như 1: Tính tổng giá trị dựa trên phối hợp từng phần

Giả sử nếu muốn tính tổng số tài chánh toàn bộ các món đồ là chuối, áp dụng phương pháp SUMIF bên dưới đây:

=SUMIF(A2:A8, “*chuối*”, C2:C8) - điều kiện bao gồm phần text được đặt trong vệt hoa thị (*).=SUMIF(A2:A8, “*”&F1&“*”, C2:C8) – điều kiện bao hàm những ô tmê say chiếu được đặt trong vệt hoa thị, thực hiện dấu (&) trước với sau đó 1 ttê mê chiếu ô nhằm nối chuỗi.

Nếu chỉ muốn đếm đa số ô ban đầu hoặc kết thúc bằng một text rõ ràng nào kia, chỉ việc thêm * vào trước hoặc sau text:

=SUMIF(A2:A8, “chuối*”, C2:C8) - tổng giá trị trong C2:C8 nếu một ô tương xứng trong cột A bắt đầu bởi tự “chuối”.=SUMIF(A2:A8, “*chuối”, C2:C8) - tổng giá trị vào C2:C8 nếu một ô tương xứng vào cột A chấm dứt bằng trường đoản cú “chuối”.

Mẹo: 

Sử dụng tân oán tử nối (&) để nối các cam kết từ bỏ đại diện với tđê mê chiếu ô. Trong ví dụ trên, chúng ta có thể áp dụng phương pháp sau nhằm tính tổng các mặt hàng “chuối” trong kho:

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

lấy một ví dụ 2. Tính tổng mức vốn cùng với các ký từ nhất định

Trong ngôi trường hợp nếu như muốn tính tổng giá trị một số trong những ký từ, chính xác là 6 cam kết trường đoản cú, các bạn thực hiện phương pháp dưới đây:

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

lấy ví dụ như 3. Tính tổng các ô tương xứng cùng với những quý hiếm text

Nếu bảng tính của công ty chứa nhiều loại dữ liệu khác nhau, cùng bạn chỉ muốn tính tổng các ô tương xứng cực hiếm text, sử dụng cách làm SUMIF bên dưới đây:

=SUMIF(A2:A8,“?*”, C2:C8) - cùng quý hiếm trong những ô C2:C8 ví như một ô khớp ứng trong cột A cất tối thiểu 1 hình tượng.=SUMIF(A2:A8,“*”,C2:C8) - bao gồm các ô trống, chứa những chuỗi độ dài bởi 0 được trả về vì những công thức khác, ví dụ: = “”.

Cả 2 phương pháp trên mọi bỏ qua những giá trị chưa hẳn là text, ví dụ như các lỗi, những phnghiền toán thù ngắn gọn xúc tích, số cùng tháng ngày.

Ví dụ 4: Sử dụng các ký kết từ * hoặc ? nhỏng các ký từ bình thường

Nếu ước ao sử dụng các cam kết trường đoản cú * hoặc ? giống hệt như những ký từ thông thường chứ không hẳn các ký kết tự đại diện thay mặt, chỉ việc thêm tân oán tử (~) trước kia.

Ví dụ: cách làm SUMIF dưới đây sẽ thêm các quý hiếm vào ô C2:C8 trường hợp một ô vào cột A tương xứng, vị trí và một sản phẩm chứa lốt ?:

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

2.5 Tính tổng vốn lớn nhất hoặc nhỏ tuổi nhất vào một vùng dữ liệu

Để tính tổng lớn nhất hoặc nhỏ độc nhất trong một vùng, áp dụng phối kết hợp hàm SUM cùng với các hàm LARGE hoặc SMALL. 

lấy ví dụ 1: Thêm cực hiếm lớn số 1 / giá trị bé dại nhất

Nếu muốn tính tổng các quý giá lớn nhất, bé dại tốt nhất, ví dụ như 5, bạn có thể nhập thẳng giá trị nhỏng trong bí quyết nlỗi dưới đây:

=SUM(LARGE(B1:B10,1,2,3,4,5)) - tổng của 5 giá trị phệ nhất=SUM(SMALL(B1:B10,1,2,3,4,5)) - tổng của 5 quý giá bé dại nhất

Lưu ý: Nếu tất cả 2 hoặc nhiều giá trị như là nhau, thì chỉ có mức giá trị trước tiên được tính. 

lấy một ví dụ 2. Tính tổng vốn bên trên / dưới 

Nếu mong mỏi tính tổng các quý giá, cụ vị liệt kê toàn bộ các cực hiếm này vào phương pháp, bạn có thể lồng các hàm ROW cùng INDIRECT vào công thức SUM. Trong hàm INDIRECT thực hiện số sản phẩm thay mặt đại diện mang lại cực hiếm cơ mà bạn có nhu cầu thêm.

Ví dụ những bí quyết khớp ứng tiếp sau đây tính tổng vốn trên và dưới 15:

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

Vì đó là những cách làm mảng, lưu giữ truy cập những mảng bằng phương pháp áp dụng tổng hợp phím Ctrl + Shift + Enter.

Ví dụ 3. Tính tổng giá trị lớn nhất / nhỏ dại nhất của những biến

Nếu không thích buộc phải biến đổi bí quyết các lần tính tổng mức vốn những ô khác nhau, bạn có thể nhập quý giá trong một ô nào đó, nắm vị nhtràn lên cách làm. Sau đó sử dụng hàm ROW với INDIRECT, tđê mê chiếu một ô chứa đổi mới, vào ví dụ này là ô E1:

=SUM(LARGE(B1:B50,ROW(INDIRECT("1:"&E1)))) - tổng những biến hóa những quý hiếm trên cùng.=SUM(SMALL(B1:B50,ROW(INDIRECT("1:"&E1)))) - tổng những thay đổi các quý giá dưới thuộc.

Cần xem xét một điều đó là những phương pháp mảng cần các bạn sẽ đề xuất thực hiện tổ hợp phím Ctrl + Shift + Enter để hoàn tất.

2.6 Cách tính tổng những ô khớp ứng với những ô trống

Ô trống là những ô ko đựng gì, không tồn tại cách làm, không tồn tại chuỗi ký kết trường đoản cú bao gồm độ dài bởi không, được trả về bởi vì một hàm Excel không giống, các bạn thực hiện "=" làm cho điều kiện, nlỗi trong cách làm SUMIF bên dưới đây:

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

Nếu ô trống bao gồm những chuỗi có độ lâu năm bởi ko (ví dụ: các ô bao gồm bí quyết nlỗi = ""), áp dụng "" làm điều kiện:

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

Trong cả 2 cách làm trên reviews những ô trong cột A cùng nếu phân phát hiện tại gồm ngẫu nhiên ô trống như thế nào, những quý giá khớp ứng sẽ được tiếp tế cột C.

2.7 Cách tính tổng các ô khớp ứng cùng với các ô chưa hẳn trống

Nếu ý muốn tính tổng mức những ô trong cột C khi một ô tương xứng trong cột A là ô trống, sử dụng "" làm cho điều kiện vào công thức SUMIF:

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

Công thức trên đếm các quý giá tương xứng cùng với toàn bộ những ô ko trống, bao hàm các chuỗi ký kết từ gồm độ dài bằng ko.

2.8 Cách sử dụng hàm SUMIF với ĐK ngày tháng

Về cơ phiên bản áp dụng hàm SUMIF để tính tổng những giá trị dựa trên điều kiện tháng ngày giống như biện pháp áp dụng hàm SUMIF với các điều kiện chuẩn nhỏng text và số.

Nếu hy vọng tính tổng mức khớp ứng với các ngày lớn hơn, nhỏ tuổi rộng hoặc bằng cùng với ngày mà lại chúng ta hướng đẫn, chỉ việc áp dụng những toán tử mà lại oimlya.com.vn giới thiệu tại vị trí bên trên. 

Dưới đó là một vài ba ví dụ về phương pháp Excel SUMIF:

Trường thích hợp nếu như muốn tính tổng các quý hiếm dựa vào ngày hiện tại, các bạn sẽ nên áp dụng phối hợp hàm SUMIF cùng với hàm TODAY() nhỏng trong bảng bên dưới đây:

Ảnh chụp screen tiếp sau đây sử dụng bí quyết sau cùng để tính tổng thể chi phí những thành phầm được ship vào tuần.

2.9 Cách tính tổng mức vốn vào phạm vi ngày cố kỉnh thể

Khá các người dùng đưa ra câu hỏi cách tính tổng mức giữa 2 ngày như thế nào?

Câu trả lời là áp dụng kết hợp hoặc đúng mực rộng là sử dụng 2 hàm SUMIF khác biệt. Trong Excel 2007 hoặc cao hơn nữa, bạn cũng có thể thực hiện hàm SUMIFS cho nhiều điều kiện. Tuy nhiên trong nội dung bài viết này oimlya.com.vn đã chỉ ra mắt cho chính mình về hàm SUMIF.

Dưới đấy là ví dụ về bí quyết SUMIF:

Hàm SUMIF trước tiên tính quý hiếm những ô C2:C9 trong đó ô tương xứng trong cột B lớn hơn hoặc bởi ngày đầu tiên (trong ví dụ trên là ngày 1/10). Sau đó trừ đi những quý hiếm lâm vào hoàn cảnh sau ngày ở đầu cuối (ngày 31/10) được trả về vì hàm SUMIF trang bị nhị.

Xem thêm: Những Cách Làm Nước Bún Chả Quạt, Bún Thịt Nướng Chất Lượng, Cách Pha Nước Chấm Bún Chả Ngon Như Người Hà Nội

2.10 Tính tổng mức vốn trong các cột

Để làm rõ về vụ việc, thuộc để ý ví dụ sau đây. Giả sử bạn bao gồm một bảng tổng kết lợi nhuận bán sản phẩm mỗi tháng. Vì có tương đối nhiều sản phẩm không giống nhau và được cung cấp sinh sống nhiều siêu thị khác biệt buộc phải bạn có nhu cầu tính tổng lệch giá một sản phẩm một:

Chẳng hạn như bạn muốn tính toàn bô hãng apple phân phối nghỉ ngơi toàn bộ những siêu thị vào 3 tháng qua.

Nhỏng chúng ta sẽ biết, kích cỡ của sum_range được xác định bởi kích cỡ tđắm đuối số range. Đó là nguyên do vì sao các bạn quan trọng sử dụng công thức nhỏng =SUMIF(A2:A9,“apples”,C2:E9), vì chưng bí quyết này sẽ thêm những giá trị tương ứng “táo” trong cột C. Đây chưa hẳn là những gì mà lại ai đang tìm kiếm kiếm.

Giải pháp hợp lí tốt nhất là tạo ra một cột prúc nhằm tính tổng từng món đồ một, tiếp nối tđê mê chiếu cột phụ đó vào sum_range.

Nhập công thức SUM dễ dàng trong ô F2, tiếp nối nhập lệ cột F công thức: = SUM (C2: E2).

Bước tiếp theo bây chừ là viết cách làm SUMIF nhỏng bên dưới đây:

=SUMIF(A2:A9, “táo”, F2:F9)

Hoặc:

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

Trong các phương pháp trên, range với sum_range bao gồm cùng số hàng cùng số cột, bao gồm một cột và 8 chiếc, với trả về kết quả:

Nếu muốn tính tổng vốn nhưng không bắt buộc sản xuất cột phú, cách đơn giản dễ dàng là viết từng bí quyết SUMIF riêng rẽ cho những cột nhưng bạn có nhu cầu tính tổng, sau đó thực hiện hàm SUM nhằm tính tổng:

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

Hoặc cách không giống là thực hiện công thức mảng phức hợp hơn (hãy nhớ là thực hiện tổng hợp phím Ctrl + Shift + Enter):

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

Cả 2 phương pháp bên trên phần đa trả về tác dụng là 2070.

Tại sao hàm SUMIF không trả về kết quả?

Có những nguyên do khiến cho hàm SUMIF không vận động, ko trả về hiệu quả. Đôi khi công thức mà bạn sử dụng không trả về cực hiếm nhưng mà chúng ta mong chờ hoàn toàn có thể là vì hình dáng tài liệu trong ô hoặc tmê man số không yêu thích hợp với hàm SUMIF. Nếu không muốn xẩy ra lỗi, dưới đấy là đều gì bạn phải kiểm tra:

1. Các tsay mê số phạm vi cùng sum_range cần là vùng tài liệu chứ đọng không phải mảng.

Các tsi mê số trước tiên (range) cùng sản phẩm cha (sum_range) vào bí quyết SUMIF đề xuất là tđê mê chiếu vùng, ví dụ như A1: A10. Nếu là mảng, ví dụ như 1,2,3, Excel vẫn trả lại một thông tin lỗi.

Công thức chủ yếu xác: =SUMIF(A1:A3, “hoa”, C1:C3)

Công thức sai: =SUMIF(1,2,3, “hoa”, C1:C3)

2. Cách tính tổng những quý hiếm tự sheet hoặc bảng tính khác.

Giống nlỗi hầu hết những hàm Excel khác, hàm SUMIF rất có thể tmê say chiếu các sheet hoặc các bảng tính không giống, miễn sao những sheet cùng bảng tính đang mnghỉ ngơi.

Ví dụ: Công thức sau đây tính tổng những cực hiếm trong số ô F2:F9 trong Sheett 1 của Book 1 giả dụ một ô tương xứng trong cột A phía trong cùng sheet tất cả chứa món đồ là “táo”:

=SUMIF(Sheet1!$A$2:$A$9,“táo”,Sheet1!$F$2:$F$9)

Tuy nhiên công thức bên trên sẽ không hoạt động nếu Book 1 bị đóng. Như vậy xẩy ra vị vùng tài liệu được tmê man chiếu bởi công thức SUMIF trong các bảng tính không được tđam mê chiếu mang lại các mảng, cùng bởi không tồn tại mảng làm sao được tmê say chiếu trong những tsay đắm số range với sum_range, Excel đã trả về thông tin lỗi #VALUE! .

3. Để rời lỗi xẩy ra, đảm bảo range với sum_range đề xuất cùng kích thước.

Trong các phiên bản Excel bắt đầu rộng, tđam mê số range cùng sum_range ko độc nhất vô nhị thiết bắt buộc bao gồm cùng size. Trong phiên bạn dạng Excel 2000 và các phiên bản cũ rộng, giả dụ kích thước của range cùng sum_range ko bằng nhau đã gây ra lỗi. 

Tuy nhiên trong số phiên phiên bản Excel mới hơn như Excel 2010 cùng Excel năm 2016, bí quyết SUMIF phức tạp rộng những, trong những số ấy tđắm say số sum_range bao gồm số hàng, số cột thấp hơn đối với tđam mê số range. Đó là nguyên do tại vì sao các tđê mê số range với sum_range đề nghị cùng kích cỡ, nhằm tránh lỗi xẩy ra.

Cách tránh lỗi VALUE Lúc áp dụng hàm SUMIF giữa các bảng tính

khi áp dụng hàm SUMIF thân các bảng tính, vào ngôi trường hòa hợp ví như bảng tính mối cung cấp ko msinh sống, bạn sẽ cảm nhận thông tin lỗi VALUE. Lỗi này xảy ra nếu các ô vào bảng tính cất cách làm có các hàm SUMIF, COUNTIF, hoặc hàm COUNTBLANK bị đóng. Để tách lỗi này bằng phương pháp sử dụng phối hợp hàm SUM cùng hàm IF trong một công thức mảng.

Công thức mảng là phương pháp có thể triển khai các phxay tính bên trên một hoặc nhiều mục vào một mảng. Các cách làm mảng vận động trên nhị xuất xắc các cực hiếm được Gọi là tđắm say số mảng.

Trong ví dụ bên dưới đó là tính tổng doanh thu các các loại sản phẩm tbỏ hải sản. Kết trái được trả về vào bảng tính có chứa công thức với tài liệu nguồn phía trong bảng tính dữ liệu.

Lưu ý: Quý Khách hoàn toàn có thể áp dụng cách này mang đến Excel 2010, 2013, năm 2016 cho Windows.

Cách 1: Msinh hoạt bảng tính đựng tài liệu nguồn (Data workbook).Bước 2: Mnghỉ ngơi bảng tính bao gồm cất bí quyết (Report workbook).Bước 3: Chọn ô C5 trong bảng tính tất cả cất công thức.Bước 4: Sử dụng nút FX bên trên tkhô giòn bí quyết (Formula Bar), xác xác định trí hàm Sum.Bước 5: Để lồng hàm IF, bên trên tkhô giòn công thức, trên mục Name Box, từ bỏ menu bạn chọn IF.Bước 6: Nếu hàm IF ko hiển thị vào thực đơn, bạn lựa chọn More Functions rồi tìm kiếm với lựa chọn hàm IF.Bước 7: Nhập những đối số nhỏng bên dưới đây:

Logical_thử nghiệm : Data.xlsx!$A$23:$A$30=”Seafood”.Value_If _true: SUM(Data.xlsx!$D$23:$D$30).Value_if_false: 0.

Bước 8: Để hoàn chỉnh công thức mảng, áp dụng tổng hợp phím Ctrl + Shift + Enter.Bước 9: Chọn Yes giả dụ được đặt ra những câu hỏi phương pháp đang đúng đắn không. Tên vùng dữ liệu (name range) hoàn toàn có thể xác minh mang đến CategoryNames cùng ProductSales.

Bằng phương pháp trên chúng ta không còn đề xuất lo lắng về lỗi sẽ xảy ra nữa, điều này giúp cho bạn tiết kiệm được một khoảng chừng thời gian đáng kể.

Trên đây là oimlya.com.vn thực hiện SUMIF (một các loại hàm cơ bản trong excel) với cùng một ĐK là tính phú cấp cho cho những người tất cả chuyên dụng cho là nhân viên, để thực hiện hàm tính tổng với nhiều ĐK thì bạn gửi sang trọng sử dụng SUMIFS. 

Bên cạnh đó bạn một hàm điều kiện tương tự như SUMIF là IF cũng rất được người dùng Excel thực hiện thông dụng, tuy nhiên hàm IF chỉ truy vấn vấn điều kiện với đưa ra công dụng cân xứng cùng với ĐK chđọng quanh đó tổng nhỏng hàm SUMIF, các bạn buộc phải chú ý để vận dụng đến đúng nhé.

Qua ví dụ trên chắc rằng chúng ta đã hiểu được giải pháp dùng hàm SUMIF nằm trong bảng tính Excel. Nhờ này mà chúng ta cũng có thể vận dụng hàm cơ phiên bản vào Excel này vào các bước của bản thân mình kết quả tốt nhất.

Xem thêm: Như Thế Nào Là Tiểu Đường - Chỉ Số Bao Nhiêu Thì Bị Tiểu Đường

Hàm cơ bản SUMIF hàm tính tổng có điều kiện cùng phương pháp áp dụng hàm SUMIF để tính toán thù có điều kiện. Để sử dụng xuất sắc hàm cơ bạn dạng Vlookup - hàm tra cứu kiếm trong Excel, những chúng ta có thể tham khảo giải pháp áp dụng Hàm Vlookup đã có công ty chúng tôi chỉ dẫn nhé.

https://oimlya.com/cach-dung-ham-sumif-trong-excel-636n.aspx Video hướng dẫn


Chuyên mục: Kiến thức