Buzz
- CHÚ Ý: Tải ngay 70+ bài tập Excel cơ bản và nâng cao
SUMIF, hàm tính tổng có điều kiện trong Excel, giúp bạn tổng hợp các giá trị theo điều kiện duy nhất. Điều kiện có thể áp dụng cho ngày, số, và văn bản, hỗ trợ cả các phép tính logic (>, <,><>, =) và ký tự đại diện (*,?) để phù hợp và trả về tổng giá trị tương ứng.
Hướng Dẫn Sử Dụng Hàm SUMIF, Cú Pháp và Ví Dụ Minh Họa
Đọc tới đây, bạn có thắc mắc về sự khác biệt giữa hàm SUMIF và SUMIFS không? Để giải đáp, hãy lưu ý rằng, SUMIFS cũng là hàm tính tổng. Tuy nhiên, so với SUMIF chỉ tính tổng các ô theo 1 điều kiện, SUMIFS tính tổng theo 'nhiều' điều kiện khác nhau.
Sử Dụng Hàm SUMIF để Tính Tổng Theo Điều Kiện với Ví Dụ Minh Họa
1. Định Nghĩa Hàm SUMIF
Hàm SUMIF giúp tổng các giá trị trong phạm vi thỏa mãn điều kiện. Khi sử dụng, bạn có thể:
- Nhanh chóng tính tổng dãy số theo điều kiện xác định.
- Kết hợp với nhiều hàm khác trong Excel.
- Linh hoạt và thuận tiện trong công việc.
2. Cấu trúc hàm SUMIF trong EXCEL
Cấu trúc hàm SUMIF: =SUMIF(range, criteria, sum_range)
- Criteria (Tiêu Chí): Điều kiện xác định giá trị tổng. Có thể là số, biểu thức, hoặc chuỗi văn bản.
- Lưu Ý
- Do tính toán trong ô của Excel, nên hàm SUMIF tính tổng này áp dụng cấu trúc giống nhau trên các phiên bản Excel như 2016, 2013, và các đời trước như 2010, 2007, 2003.
***** Ví Dụ sử dụng hàm SumIF tính tổng phụ cấp cho nhân viên *****
- Bài toán hàm SUMIF: Tính tổng phụ cấp cho những người có chức vụ là 'Nhân Viên' trong bảng số liệu dưới đây:
- Với công thức cho ô D15 là: =SUMIF(C5:C14,'Nhân Viên',D5:D14)
=> Kết quả: 2500000. Kiểm tra lại, có 5 người có chức vụ 'Nhân viên', tổng phụ cấp chính xác là 2500000.
* Những điều cần lưu ý khi sử dụng hàm SUMIF:
- Nếu bỏ qua sum_range, tổng sẽ được tính theo Range (thay thế bằng các ô trong phạm vi đánh giá).
- SUMIF không phân biệt chữ hoa chữ thường. Bạn có thể tính tổng các giá trị trong đó dữ liệu text có thể là 'Mytour' hoặc 'MYTOUR'.
3. Hướng dẫn cách sử dụng hàm SUMIF trong EXCEL
Dưới đây là một số công thức của hàm Excel SUMIF, cách sử dụng hàm tính tổng có điều kiện SUMIF, bài tập hàm SUMIF với các điều kiện và các bộ dữ liệu khác nhau mà bạn có thể tham khảo
3.1. Các điều kiện so sánh với SUMIF
- Dưới đây là một số công thức SUMIF để so sánh giá trị lớn hơn, nhỏ hơn hoặc bằng với giá trị cho trước.
+ Lưu ý: Trong các công thức Excel SUMIF, sau toán tử so sánh là một số hoặc văn bản phải luôn được đặt trong dấu ngoặc kép ('').
3.2. Sử dụng hàm SUMIF với điều kiện là text
- Hàm SUMIF giúp bạn thêm giá trị tùy thuộc vào ô tương ứng trong cột khác có chứa text hoặc không.
+ Chú ý: Bạn sẽ sử dụng các công thức SUMIF khác nhau để đối sánh chính xác hoặc đối sánh từng phần, các công thức này được liệt kê trong bảng dưới đây.
Tiếp theo, hãy xem cách công thức SUM không tương đương hoạt động trong ví dụ dưới đây, tổng số tiền của tất cả các mặt hàng không phải là “goldfinger bananas”:
=SUMIF(A2:A8,'<> goldfinger bananas', C2:C8)
Lưu ý: Tương tự các hàm Excel khác, hàm SUMIF không phân biệt chữ hoa và chữ thường. Điều này có nghĩa là dù bạn nhập “<>bananas”, “<>Bananas” hay “<>BANANAS”, kết quả cuối cùng vẫn giống nhau.
3.3. Sử dụng toán tử so sánh với tham chiếu ô
- Nếu bạn muốn tổng hợp công thức SUMIF chung, bạn có thể thay thế giá trị số hoặc văn bản trong các điều kiện bằng tham chiếu ô:
=SUMIF(A2:A8,'<>'&F1, C2:C8)
Trong trường hợp này, bạn không cần sửa đổi công thức để tính tổng giá trị dựa trên các tiêu chí khác. Chỉ cần nhập một giá trị mới vào ô được tham chiếu.
Lưu ý: Khi bạn sử dụng biểu thức logic với tham chiếu ô, hãy sử dụng dấu ngoặc kép (“”) để bắt đầu chuỗi văn bản và dấu và (&) để nối và kết thúc chuỗi, như ví dụ “<>” & F1.
Không cần sử dụng toán tử “bằng” (=) trước tham chiếu ô. Vì vậy, cả hai công thức dưới đây đều đúng và cho kết quả tương đương 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 với ký tự đại diện
Nếu mục đích của bạn là tổng hợp giá trị các ô dựa trên điều kiện “text” và bạn muốn đối sánh chỉ một phần, trong trường hợp này, bạn sẽ phải sử dụng các ký tự đại diện trong công thức SUMIF.
Dưới đây là một số ký tự đại diện có sẵn:
- Dấu hoa thị (*) - đại diện cho bất kỳ ký tự nào
- Dấu hỏi chấm (?) - đại diện cho một ký tự duy nhất tại một vị trí cụ thể
Ví dụ 1: Tính tổng giá trị dựa trên kết hợp từng phần
Giả sử bạn muốn tính tổng số tiền của tất cả các mặt hàng là chuối, hãy sử dụng công thức SUMIF dưới đây:
=SUMIF(A2:A8, “*chuối*”, C2:C8) - điều kiện bao gồm phần text được đặt trong dấu hoa thị (*).
=SUMIF(A2:A8, “*”&F1&“*”, C2:C8) – điều kiện bao gồm các ô tham chiếu được đặt trong dấu hoa thị, sử dụng dấu (&) trước và sau một tham chiếu ô để nối chuỗi.
Nếu bạn chỉ muốn đếm những ô bắt đầu hoặc kết thúc bằng một text cụ thể nào đó, chỉ cần 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 ứng trong cột A bắt đầu bằng từ “chuối”.
=SUMIF(A2:A8, “*chuối”, C2:C8) - tổng giá trị trong C2:C8 nếu một ô tương ứng trong cột A kết thúc bằng từ “chuối”.
Mẹo:
Sử dụng toán tử nối (&) để kết hợp các ký tự đại diện với tham chiếu ô. Trong ví dụ này, bạn có thể áp dụng công thức sau để tính tổng các mặt hàng “chuối” trong kho:
=SUMIF(A2:A8, '*'&F1&'*', C2:C8)
Ví dụ 2: Tính tổng giá trị với các ký tự nhất định
Nếu muốn tính tổng giá trị của một số ký tự cụ thể, chẳng hạn là 6 ký tự, bạn có thể áp dụng công thức sau:
=SUMIF(A2:A8, '??????', C2:C8)
Ví dụ 3: Tính tổng các ô tương ứng với các giá trị văn bản
Nếu bảng tính của bạn chứa nhiều loại dữ liệu khác nhau và bạn chỉ muốn tính tổng các ô tương ứng với giá trị văn bản, sử dụng công thức SUMIF dưới đây:
=SUMIF(A2:A8,'?*', C2:C8) - tổng giá trị trong các ô C2:C8 nếu một ô tương ứng trong cột A chứa ít nhất một ký tự.
=SUMIF(A2:A8,'*',C2:C8) - bao gồm các ô trống, chứa các chuỗi có độ dài bằng 0 được trả về bởi các công thức khác, ví dụ: = “”.
Cả hai công thức trên đều loại bỏ các giá trị không phải là văn bản, chẳng hạn như lỗi, phép toán logic, số và ngày tháng.
Ví dụ 4: Sử dụng ký tự * hoặc ? như các ký tự bình thường
Nếu muốn sử dụng ký tự * hoặc ? như các ký tự thông thường thay vì ký tự đại diện, chỉ cần thêm toán tử (~) phía trước.
Ví dụ: công thức SUMIF dưới đây sẽ tổng hợp các giá trị trong ô C2:C8 nếu một ô tương ứng trong cột A, trên cùng một hàng, chứa dấu ?
=SUMIF(A2:A8, '~?', C2:C8)
2.5. Tính tổng giá trị lớn nhất hoặc nhỏ nhất trong một phạm vi dữ liệu
Để tính tổng giá trị lớn nhất hoặc nhỏ nhất trong một phạm vi, sử dụng kết hợp hàm SUM với các hàm LARGE hoặc SMALL.
Ví dụ 1: Tính tổng giá trị lớn nhất / giá trị nhỏ nhất
Nếu bạn muốn tính tổng các giá trị lớn nhất, nhỏ nhất, ví dụ như 5, bạn có thể nhập trực tiếp giá trị trong công thức như dưới đây:
=SUM(LARGE(B1:B10,{1,2,3,4,5})) - tổng của 5 giá trị lớn nhất
=SUM(SMALL(B1:B10,{1,2,3,4,5})) - tổng của 5 giá trị nhỏ nhất
Chú ý: Nếu có 2 hoặc nhiều giá trị giống nhau, chỉ có giá trị đầu tiên được tính.
Ví dụ 2. Tính tổng giá trị trên / dưới
Nếu bạn muốn tính tổng nhiều giá trị, thay vì liệt kê tất cả chúng trong công thức, bạn có thể lồng các hàm ROW và INDIRECT trong công thức SUM. Trong hàm INDIRECT, sử dụng số hàng để đại diện cho giá trị bạn muốn thêm vào.
Ví dụ về các công thức tương ứng dưới đây tính tổng giá trị trên và dưới 15:
=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, nhớ truy cập các mảng bằng cách sử dụng tổ hợp phím Ctrl + Shift + Enter.
Ví dụ 3. Tính tổng giá trị lớn nhất / nhỏ nhất của các biến
Nếu bạn không muốn phải thay đổi công thức mỗi khi tính tổng giá trị các ô khác nhau, bạn có thể nhập giá trị trong một ô nào đó thay vì nhập vào công thức. Sau đó sử dụng hàm ROW và INDIRECT, tham chiếu một ô chứa biến (trong ví dụ này là ô E1):
=SUM(LARGE(B1:B50,ROW(INDIRECT('1:'&E1)))) - tổng các giá trị lớn nhất theo biến ở trên cùng.
=SUM(SMALL(B1:B50,ROW(INDIRECT('1:'&E1)))) - tổng các giá trị nhỏ nhất theo biến ở dưới cùng.
Cần chú ý rằng đây là các công thức mảng, bạn cần sử dụng tổ hợp phím Ctrl + Shift + Enter để hoàn tất.
2.6. Cách tính tổng các ô tương ứng với các ô trống
Các ô trống là những ô không chứa dữ liệu, không có công thức, không chứa chuỗi có độ dài bằng không. Để tính tổng các ô trống, sử dụng điều kiện '=' trong công thức SUMIF sau đây:
=SUMIF(A2:A10,'=',C2:C10)
Khi ô trống chứa chuỗi có độ dài bằng không (ví dụ: các ô có công thức như = ''), sử dụng điều kiện '' trong công thức sau:
=SUMIF(A2:A10,'',C2:C10)
Cả hai công thức trên kiểm tra các ô trong cột A và nếu phát hiện bất kỳ ô trống nào, giá trị tương ứng sẽ được thêm vào cột C.
2.7. Tính tổng các ô tương ứng với các ô không trống
Để tính tổng giá trị của các ô trong cột C khi ô tương ứng trong cột A là ô trống, hãy sử dụng điều kiện '<>' trong công thức SUMIF sau:
=SUMIF(A2:A10,'<>',C2:C10)
Công thức này đếm các giá trị liên quan đến tất cả các ô không trống, bao gồm cả chuỗi có độ dài bằng không.
2.8. Sử dụng hàm SUMIF với điều kiện ngày tháng
Tổng quát, sử dụng hàm SUMIF để tính tổng các giá trị dựa trên điều kiện ngày tháng tương tự như khi áp dụng hàm SUMIF với điều kiện chuẩn như text và số.
Muốn tính tổng giá trị liên quan đến ngày lớn hơn, nhỏ hơn hoặc bằng với ngày bạn chỉ định, chỉ cần sử dụng các toán tử được Mytour giới thiệu ở phần trước.
Dưới đây là một số ví dụ về công thức Excel SUMIF:
Nếu muốn tính tổng giá trị dựa trên ngày hiện tại, bạn có thể kết hợp hàm SUMIF với hàm TODAY() như trong bảng dưới đây:
Bằng cách sử dụng công thức độc đáo, ảnh chụp màn hình dưới đây mô tả cách tính tổng giá trị của các sản phẩm đã được vận chuyển trong tuần.
2.9. Phương pháp tính tổng giá trị trong khoảng thời gian cụ thể
Đối mặt với câu hỏi về cách tính tổng giá trị giữa 2 ngày, có nhiều người đặt ra thắc mắc về cách thực hiện điều này.
Để giải quyết vấn đề này, chúng ta có thể sử dụng kết hợp hoặc chọn cách đơn giản hơn là sử dụng 2 hàm SUMIF khác nhau. Trong Excel 2007 trở lên, bạn có thể áp dụng hàm SUMIFS để xử lý nhiều điều kiện khác nhau. Tuy nhiên, trong bài viết này, Mytour sẽ hướng dẫn bạn cách sử dụng hàm SUMIF.
Dưới đây là ví dụ về cách sử dụng hàm SUMIF:
=SUMIF(B2:B9, '>=10/1/2014', C2:C9) - SUMIF(B2:B9, '>=11/1/2014', C2:C9)
Hàm SUMIF này tính tổng giá trị trong các ô C2:C9 nếu ngày trong cột B nằm trong khoảng từ 1/10/2014 đến 31/10/2014.
Đầu tiên, hàm SUMIF tính giá trị của các ô C2:C9 mà ngày tương ứng trong cột B lớn hơn hoặc bằng ngày đầu tiên (1/10/2014). Sau đó, trừ đi tổng giá trị của các ô rơi vào sau ngày cuối cùng (31/10/2014) do hàm SUMIF thứ hai trả về.
2.10. Tính tổng giá trị trong các cột
Để bắt đầu, hãy xem xét một ví dụ thú vị. Giả sử bạn quản lý một bảng tổng kết doanh số bán hàng hàng tháng với nhiều mặt hàng và nhiều cửa hàng. Nếu bạn muốn tính tổng doanh số của một sản phẩm cụ thể như số táo bán ra từ tất cả cửa hàng trong 3 tháng qua, bạn sẽ làm như sau:
Ví dụ, bạn có thể muốn biết tổng số lượng táo đã bán ra từ tất cả các cửa hàng trong khoảng thời gian 3 tháng trở lại.
Chú ý rằng kích thước của sum_range phải phản ánh kích thước của range. Điều này có nghĩa là bạn không thể sử dụng công thức như =SUMIF(A2:A9, 'táo', C2:E9) vì nó sẽ thêm tất cả các giá trị 'táo' trong cột C. Hãy lưu ý điều này để tránh những kết quả không mong muốn.
Để giải quyết vấn đề này, cách tốt nhất là tạo một cột phụ để tính tổng từng mặt hàng một và sau đó sử dụng cột phụ đó trong sum_range.
Trong ô F2, nhập đơn giản công thức SUM, sau đó mở rộng công thức đó xuống cột F: = SUM (C2: E2).
Bước tiếp theo là viết công thức SUMIF như sau:
=SUMIF(A2:A9, “táo”, F2:F9)
Hoặc:
=SUMIF(A2:A9, H1, F2:F9)
Trong những công thức trên, range và sum_range có cùng số hàng và số cột, tạo thành một lưới 1 cột và 8 dòng, mang lại kết quả:
Nếu muốn tính tổng giá trị mà không cần tạo cột phụ, có một cách đơn giản là viết từng công thức SUMIF riêng cho mỗi cột bạn muốn tính tổng, sau đó sử dụng hàm SUM để tổng hợp:
=SUM(SUMIF(A2:A9,I1,C2:C9), SUMIF(A2:A9,I1,D2:D9), SUMIF(A2:A9,I1,E2:E9))
Hoặc bạn có thể sử dụng một cách khác, sử dụng công thức mảng phức tạp hơn (nhớ sử dụng tổ hợp phím Ctrl + Shift + Enter):
{=SUM((C2:C9+D2:D9+E2:E9)*(--(A2:A9=I1)))}
Cả hai công thức trên đều đưa ra kết quả là 2070.
4. Một số điều cần lưu ý khi sử dụng hàm SUMIF
Có nhiều lý do khiến hàm SUMIF không hoạt động hoặc không trả về kết quả mong muốn. Có thể là do công thức bạn sử dụng không trả về giá trị như bạn mong đợi, có thể là do kiểu dữ liệu trong ô hoặc tham số không phù hợp với hàm SUMIF. Để tránh lỗi, dưới đây là những điều bạn cần kiểm tra:
1. Đảm bảo rằng các tham số phạm vi và sum_range là vùng dữ liệu chứ không phải mảng.
Cả hai tham số đầu tiên (range) và thứ ba (sum_range) trong công thức SUMIF cần phải là tham chiếu vùng, ví dụ như A1:A10. Nếu chúng là mảng, như {1,2,3}, Excel sẽ báo lỗi.
Công thức đúng: =SUMIF(A1:A3, “hoa”, C1:C3)
Công thức không đúng: =SUMIF({1,2,3}, “hoa”, C1:C3)
2. Cách tính tổng các giá trị từ sheet hoặc bảng tính khác.
Tương tự như hầu hết các hàm Excel khác, hàm SUMIF có thể tham chiếu đến các sheet hoặc bảng tính khác, miễn là các sheet và bảng tính đang mở.
Ví dụ: Dưới đây là công thức tính tổng các giá trị trong các ô F2:F9 trong Sheet 1 của Book 1 nếu ô tương ứng trong cột A nằm trong cùng sheet chứa mặt hàng 'táo':
=SUMIF([Book1.xlsx]Sheet1!$A$2:$A$9, 'táo', [Book1.xlsx]Sheet1!$F$2:$F$9)
Tuy nhiên, công thức này sẽ không hoạt động nếu Book 1 bị đóng. Nguyên nhân là vùng dữ liệu được tham chiếu bởi công thức SUMIF không liên quan đến các mảng. Vì không có mảng nào được tham chiếu trong các tham số range và sum_range, Excel sẽ trả về lỗi #VALUE!.
3. Để tránh lỗi này, hãy đảm bảo rằng range và sum_range có cùng kích thước.
Trong các phiên bản Excel mới hơn, không nhất thiết phải có cùng kích thước cho các tham số range và sum_range. Tuy nhiên, trong Excel 2000 và các phiên bản cũ hơn, lỗi sẽ xảy ra nếu kích thước không khớp.
Trái lại, trong Excel 2010 và Excel 2016, công thức SUMIF phức tạp hơn, với tham số sum_range có số hàng, số cột ít hơn so với tham số range. Điều này giải thích vì sao cần đảm bảo rằng range và sum_range có cùng kích thước để tránh lỗi.
Cách tránh lỗi VALUE khi sử dụng hàm SUMIF giữa các bảng tính
Khi sử dụng hàm SUMIF giữa các bảng tính, nếu bảng tính nguồn không mở, bạn có thể gặp thông báo lỗi VALUE. Để tránh lỗi này, hãy sử dụng kết hợp hàm SUM và hàm IF trong một công thức mảng.
Công thức mảng là công thức có khả năng thực hiện nhiều phép tính trên một hoặc nhiều mục trong một mảng. Các công thức mảng thường làm việc trên hai hoặc nhiều giá trị, được gọi là tham số mảng.
Ví dụ, dưới đây là cách tính tổng doanh thu từ các loại mặt hàng thủy hải sản. Kết quả sẽ hiển thị trong bảng tính, bao gồm cả công thức và dữ liệu nguồn từ bảng tính dữ liệu.
Lưu ý: Phương pháp này áp dụng cho Excel 2010, 2013, và 2016 trên Windows.
Bước 1: Mở bảng tính chứa dữ liệu nguồn (Data workbook).
Bước 2: Mở bảng tính chứa công thức (Report workbook).
Bước 3: Chọn ô C5 trong bảng tính công thức.
Bước 4: Sử dụng nút FX trên thanh công thức (Formula Bar), xác định vị trí hàm Sum.
Bước 5: Để lồng hàm IF, trên thanh công thức, tại mục Name Box, từ menu chọn IF.
Bước 6: Nếu hàm IF không xuất hiện trong menu, bạn chọn More Functions và tìm chọn hàm IF.
Bước 7: Nhập các đối số như sau:
Logical_test: 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 tất công thức mảng, sử dụng tổ hợp phím Ctrl + Shift + Enter.
Bước 9: Chọn Yes nếu được hỏi công thức đã chính xác chưa. Tên vùng dữ liệu (name range) có thể xác định cho CategoryNames và ProductSales.
Bằng cách này, bạn không còn phải lo lắng về lỗi xảy ra, giúp tiết kiệm thời gian đáng kể.
Ở đây, Mytour sử dụng SUMIF (hàm cơ bản trong Excel) để tính phụ cấp cho nhân viên. Đối với tính tổng với nhiều điều kiện, bạn có thể chuyển sang sử dụng SUMIFS. Trong quá trình tính toán với hàm Sumif, có thể gặp lỗi cú pháp, lỗi VALUE, v.v. Để khắc phục, bạn cần tham khảo bài viết về Lỗi hàm SUMIF để hiểu và sửa lỗi.
Ngoài ra, hàm IF cũng là một lựa chọn phổ biến khác tương tự như SUMIF. Tuy nhiên, hàm IF chỉ xử lý điều kiện và trả về kết quả phù hợp với điều kiện mà không tính tổng như hàm SUMIF. Hãy chắc chắn sử dụng đúng hàm cho mục đích cụ thể.
Qua ví dụ trên, bạn đã nắm bắt cách sử dụng hàm SUMIF trong Excel. Điều này giúp bạn áp dụng hàm cơ bản của Excel một cách hiệu quả trong công việc hàng ngày.
Hàm cơ bản SUMIF giúp tính tổng theo điều kiện. Để sử dụng hàm Vlookup một cách hiệu quả, hãy tham khảo cách sử dụng Hàm Vlookup đã được hướng dẫn.
Video hướng dẫn
