Hàm SUMIF trong Excel được sử dụng để tính tổng các giá trị thỏa mãn điều kiện cho trước, vậy bạn đã biết cách sử dụng cấu trúc này một cách hiệu quả chưa? Trong bài viết này Azgad Edu sẽ giúp bạn hiểu rõ hơn về cấu trúc hàm SUMIF và chia sẻ những điều cần lưu ý để bạn áp dụng một cách dễ dàng hơn.
Công thức và ứng dụng của hàm SUMIF trong Excel
Công thức hàm SUMIF
=
SUMIF(
Range
,
Criteria
, [Sum_range])
Giải thích tham số:
- Range
(Phạm vi): Phạm vi vùng chọn chứa các ô điều kiện. Các ô trong phạm vi có thể là ô chứa số, tên hoặc mảng.
- Criteria
(Điều kiện): Điều kiện để xác định các giá trị được tính tổng. Điều kiện có thể ở dạng số, biểu thức, text hoặc 1 ô trong bảng thống kê.
- Sum_range
: Vùng cần tính tổng hoặc dải ô cần tính tổng.
Ứng dụng của hàm SUMIF
-
Hàm SUMIF được sử dụng để tính tổng các giá trị thỏa mãn một điều kiện nhất định.
-
Công thức này có thể được sử dụng kết hợp với nhiều hàm khác trong Excel mang đến sự thuận tiện khi xử lý công việc.
Cách sử dụng hàm SUMIF trong Excel
Hàm SUMIF với các điều kiện lớn hơn, nhỏ hơn hoặc bằng các điều kiện cho trước
hàm SUMIF, Azgad Edu sử dụng ví dụ minh họa về bảng thống kê số lượng hàng hóa bán được như sau:
Để hiểu rõ hơn về, Azgad Edu sử dụng ví dụ minh họa về bảng thống kê số lượng hàng hóa bán được như sau:
Ví dụ 1:
Sử dụng hàm SUMIF để tính tổng thành tiền của những hàng hóa có đơn giá trên 30.000 nghìn đồng.
Để giải quyết được bài toán này, trước hết chúng ta cần phải xác định các tham số của hàm SUMIF, cụ thể:
- Range (F2:F11)
: Vùng dữ liệu chứa đơn giá của hàng hóa.
- Criteria (“>30000”)
: Điều kiện yêu cầu của bài toán.
- Sum_range (G2:G11)
: Vùng dữ liệu chứa giá trị thành tiền cần tính tổng.
Lưu ý:
Hàm SUMIF với các điều kiện là một dấu so sánh theo sau bởi một số hoặc văn bản phải luôn được đặt trong dấu nháy kép (“ ”).
Nhập công thức
=SUMIF(F2:F11, “>30000”,G2:G11),
ta sẽ được kết quả:
Tổng thành tiền của những hàng hoá có đơn giá trên 30.000 nghìn đồng.
Cấu trúc hàm SUMIF sử dụng toán tử với tham chiếu ô
Ví dụ 2:
Sử dụng dữ liệu trên, tính tổng thành tiền những hàng hóa loại 2.
- Range (C2:C11)
: Vùng dữ liệu chứa đơn giá của hàng hóa.
- Criteria (“=”&E12)
: Điều kiện yêu cầu của bài toán cần tìm ở ô tham chiếu D12.
- Sum_range (G2:G11)
: Vùng dữ liệu chứa giá trị thành tiền cần tính tổng.
Lưu ý:
Khi sử dụng điều kiện là một biểu thức toán tử với một tham chiếu ô, bạn phải sử dụng dấu nháy kép (“ “) để bắt đầu một chuỗi văn bản và ký tự (&) để nối/kết thúc chuỗi.
Nhập công thức
=SUMIF(F2:F11,“=”&E12,G2:G11),
ta sẽ được kết quả:
Tính tổng thành tiền của những hàng hóa loại 2 trong bảng dữ liệu
Lưu ý:
Khi sử dụng toán tử bằng “=” trước một ô tham chiếu, ta có thể lược bỏ dấu bằng và công thức dưới đây cho cùng một kết quả với ví dụ trên:
=SUMIF(F2:F11,E12,G2:G11).
Ví dụ 3:
Sử dụng dữ liệu ban đầu, tính tổng thành tiền của những hàng hóa không phải là loại 2.
- Range (C2:C11)
: Vùng dữ liệu chứa loại của hàng hoá.
- Criteria (“<>”E2)
: Điều kiện yêu cầu của bài toán.
- Sum_range (G2:G11)
: Vùng dữ liệu chứa giá trị thành tiền cần tính tổng.
Lưu ý:
Trong trường hợp này việc sử dụng tiêu chí “<>” có ý nghĩa tính tổng các giá trị trong phạm vi Range chứa bất kỳ giá trị nào khác điều kiện cho trước.
Nhập công thức
=SUMIF(C2:C11,“<>”F2,G2:G11),
ta sẽ được kết quả:
Cấu trúc SUMIF với điền kiện “<>”
Công thức hàm SUMIF với các điều kiện là text
Ví dụ 4:
Sử dụng dữ liệu ban đầu, tính tổng thành tiền của những hàng hóa thuộc ngành hàng thực phẩm.
- Range (D2:D11)
: Vùng dữ liệu chứa ngành hàng của hàng hoá.
- Criteria (“Thực phẩm”)
: Điều kiện yêu cầu của bài toán.
- Sum_range (G2:G11)
: Vùng dữ liệu chứa giá trị thành tiền cần tính tổng.
Nhập công thức
=SUMIF(D2:D11,“Thực phẩm”,G2:G11),
ta sẽ được kết quả:
Tổng thành tiền của những hàng hóa thuộc ngành hàng thực phẩm
Ngoài ra ta có thể dễ dàng tính được tổng thành tiền những hàng hoá không thuộc ngành hàng thực phẩm chỉ với việc thay đổi điều kiện Criteria thành:
(“<>Thực phẩm”).
Hàm SUMIF với các ký tự đại diện trong Excel
Trong các bài toán có điều kiện ở dạng text và bạn muốn tổng hợp bằng cách đối sánh một phần, bạn cần phải sử dụng các ký tự đại diện trong Excel để giải quyết các trường hợp này.
Các ký tự đại diện trong hàm SUMIF:
-
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ể.
-
Dấu hoa thị (*): đại diện cho ký tự bất kỳ.
Ví dụ 5:
Sử dụng dữ liệu ban đầu, dùng hàm SUMIF để tính tổng thành tiền của những hàng hóa có đơn vị tính là kg.
Để giải quyết được bài toán này trước hết bạn cần xác định được các thuộc tính của bài toán, cụ thể:
- Range (B2:B11)
: Vùng dữ liệu chứa đơn vị tính của hàng hoá.
- Criteria (“*kg?”)
: Điều kiện yêu cầu của bài toán.
Ký tự hoa thị (*) đại diện cho ký tự bất kỳ ở phía trước của đơn vị cần tìm.
Ký tự hỏi chấm (?) đại diện cho một ký tự duy nhất ở phía sau của đơn vị cần tìm.
Ví dụ: Tên hàng: Bọt ngọt (kg).
– Ký tự (*) đại diện cho: “Bọt ngọt (”
– Ký tự (?) đại diện cho: “)”
- Sum_range (G2:G11)
: Vùng dữ liệu chứa giá trị thành tiền cần tính tổng.
Nhập công thức
=SUMIF(B2:B11,“*kg?”,G2:G11),
ta sẽ được kết quả:
Sử dụng hàm tính tổng có điều kiện với cái ký tự đại diện
Công thức SUMIF với điều kiện ngày tháng
Trong trường hợp này cách 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ư cách sử dụng các điều kiện như text và số.
Ví dụ 6:
Dựa vào dữ liệu thống kê hàng hoá bán được nhập trong tháng 7, tính tổng số lượng hàng hoá nhập vào trước ngày 15/07/2021.
Nhập công thức: =SUMIF(D2:D11,”<15/07/2021″,E2:E11).
Hàm tính tổng có điền kiện ngày tháng
Ví dụ 7:
Sử dụng dữ liệu trên, tính tổng số lượng hàng hoá nhập vào sau ngày 15/07/2021 trong một ô tham chiếu khác
Nhập công thức: =SUMIF(D2:D11,”>”&D12,E2:E11).
Kết quả nhận được
Tính tổng các ô tương ứng với giá trị trống
Ô trống là ô hoàn toàn không chứ gì, không chứa chuỗi ký tự và có độ dài chuỗi ký tự bằng không. Để giải quyết được bạn có thể sử dụng “=” hoặc “” làm tiêu chí.
Ví dụ 8: Sử dụng hàm SUMIF tính tổng thành tiền những hàng hoá thiếu tên trong bảng số liệu sau:
Trong trường hợp này ta có thể sử dụng 2 công thức sau đều cho cùng một kết quả.
Công thức 1:
=SUMIF(B2:B11,“=”,G2:G11).
Công thức 2:
=SUMIF(B2:B11,“”,G2:G11).
Tính tổng thành tiền những hàng hoá thiếu tên trong bảng số liệu
Vậy cũng với bảng dữ liệu này, làm thế nào để chúng ta tính tổng thành tiền những hàng hoá đã có tên? Trong trường hợp này bạn có thể sử dụng “<>” làm tiêu chí. Thao tác này sẽ cộng tất cả các ô có chuỗi ký tự, không bao gồm các ô trống.
Nhập công thức:
=SUMIF(B2:B11,“<>”,G2:G11)
để tính tổng thành tiền những hàng hoá đã có tên.
Sử dụng “<>” làm tiêu chí để tính tổng những giá trị có chứa ký tự.
Tính tổng thành tiền của những hoá đơn đã có tên
Lưu ý khi sử dụng hàm SUMIF trong Excel
Để sử dụng hàm SUMIF hiệu quả, không chỉ nắm được công thức mà bạn cần phải lưu ý những điều dưới đây để tránh xảy ra những lỗi không mong muốn.
Các tham số của Range và Sum_range phải là vùng dữ liệu chứ không phải là mảng
Hãy đảm bảo rằng tham số Range và Sum_range trong công thức hàm SUMIF phải là tham chiếu vùng chẳng hạn như A1:A10. Nếu bạn bỏ qua lưu ý này và nhập công thức mảng chẳng hạn như {1,2,3}, Excel sẽ trả lại một thông báo lỗi.
-
Công thức hợp lệ:
=SUM(B1:B10,”Thực phẩm”,G1:G10).
-
Công thức không hợp lệ:
=SUM({1,2,3},”Thực phẩm”,G1:G10).
Đảm bảo rằng Range và Sum_range có cùng kích thước để tránh xảy ra lỗi khi tính toán
-
Trong các phiên bản Excel từ 2000 trở về trước, tham số hàm Range và Sum_range có kích thước không đều nhau có thể gây ra lỗi. Trong các phiên bản hiện đại của Excel, các tham số hàm Range và Sum_range không nhất thiết phải có kích thước bằng nhau.
-
Tuy nhiên trong các phiên bản Excel 2010 và Excel 2016, việc tham số của hàm Range và Sum_range không cùng kích thước sẽ không hợp lệ. Do đó khi sử dụng hàm SUMIF bạn phải lưu ý rằng kích thước tham số của Range và Sum_range phải đồng đều nhau để tránh xảy ra lỗi.
Cách tính tổng các giá trị từ hai sheet hoặc bảng tính khác nhau
-
Tương tự như các hàm khác trong Excel, hàm SUMIF có thể tham chiếu đến các sheet hoặc bảng tính khác miễn là sheet hoặc bảng tính đó vẫn đang mở và không bị lỗi.
-
Tuy nhiên trong trường hợp sheet hoặc bảng tính tham chiếu không hoạt động, Excel sẽ trả về thông báo lỗi.
Qua ví dụ minh hoạ cùng những lưu ý trên chắc hẳn bạn đã nắm được cách sử dụng hàm SUMIF rồi phải không nào. Hy vọng bài viết trên sẽ có ích cho bạn và đừng quên truy cập Azgad Edu để tham khảo thêm nhiều công thức hữu ích khác nữa nhé!
Tác giả: Lê Thị Hoài Thương
