Hàm SUMIF trong Excel là một công cụ mạnh mẽ giúp tính tổng các giá trị dựa trên một điều kiện cụ thể. Bài viết này sẽ hướng dẫn chi tiết cách sử dụng hàm SUMIF, từ cơ bản đến nâng cao, kèm theo ví dụ minh họa dễ hiểu cho người dùng Việt Nam.
Hàm SUMIF có cú pháp cơ bản như sau:
=SUMIF(range, criteria, [sum_range])Trong đó:
range: Vùng dữ liệu chứa điều kiện cần kiểm tra.criteria: Điều kiện cần thỏa mãn.sum_range: Vùng dữ liệu chứa các giá trị cần tính tổng (tùy chọn). Nếu không cósum_range, hàm sẽ tính tổng các ô trongrangethỏa mãn điều kiện.
Sử dụng Dấu So Sánh với Tham Chiếu Ô
Để linh hoạt hơn, bạn có thể tham chiếu đến một ô chứa điều kiện thay vì nhập trực tiếp vào công thức. Ví dụ:
=SUMIF(A2:A8,""&F1,C2:C8)Trong trường hợp này, điều kiện được lấy từ ô F1. Bạn chỉ cần thay đổi giá trị trong ô F1 để tính tổng với điều kiện khác mà không cần sửa đổi công thức.
Tính tổng số lượng tồn kho của tất cả các sản phẩm khác với ô F1.
Lưu ý: Khi sử dụng biểu thức logic với tham chiếu ô, bạn cần dùng dấu nháy kép ("") để bắt đầu chuỗi văn bản và dấu và (&) để nối chuỗi.
Công thức SUMIF với Ký Tự Đại Diện
Ký tự đại diện giúp tính tổng dựa trên đối sánh một phần của chuỗi văn bản. Có hai ký tự đại diện chính:
*: Đại diện cho nhiều ký tự.?: Đại diện cho một ký tự duy nhất.
Ví dụ 1: Đối Sánh Một Phần
Để tính tổng số lượng chuối, bạn có thể dùng:
=SUMIF(A2:A8,"chuối*",C2:C8)Hoặc sử dụng tham chiếu ô:
=SUMIF(A2:A8,"*"&F1&"*",C2:C8)
Sử dụng ký tự đại diện để tính tổng giá trị dựa trên đối sánh một phần
Ví dụ 2: Số Ký Tự Cụ Thể
Để tính tổng các giá trị có 6 ký tự:
=SUMIF(A2:A8,"??????",C2:C8)Ví dụ 3: Giá Trị Văn Bản
Để tính tổng các ô chứa giá trị văn bản:
=SUMIF(A2:A8,"?*",C2:C8) Ví dụ 4: Ký Tự Đại Diện Như Ký Tự Thường
Sử dụng dấu ngã (~) trước ký tự đại diện để coi chúng như ký tự thường. Ví dụ, để tính tổng các ô chứa dấu hỏi chấm:
=SUMIF(A2:A8,"~?",C2:C8)
Công thức SUMIF cộng các giá trị tương ứng với dấu hỏi chấm trong một cột khác
Tổng Các Số Lớn Nhất hoặc Nhỏ Nhất
Kết hợp hàm SUM với LARGE hoặc SMALL để tính tổng các số lớn nhất hoặc nhỏ nhất trong một dải.
Ví dụ 1: Tổng Vài Số Lớn/Nhỏ Nhất
=SUM(LARGE(B1:B10,{1,2,3,4,5})) // 5 số lớn nhất
=SUM(SMALL(B1:B10,{1,2,3,4,5})) // 5 số nhỏ nhất
Tính tổng các số lớn nhất và nhỏ nhất trong một dải
Ví dụ 2: Tổng Nhiều Số Lớn/Nhỏ Nhất (Công thức mảng)
=SUM(LARGE(B1:B50,ROW(INDIRECT("1:15")))) // 15 số lớn nhất
=SUM(SMALL(B1:B50,ROW(INDIRECT("1:15")))) // 15 số nhỏ nhấtNhập công thức mảng bằng Ctrl + Shift + Enter.
Công thức mảng để cộng nhiều số lớn nhất / nhỏ nhất
Ví dụ 3: Tổng Số Lượng Biến Đổi (Công thức mảng)
=SUM(LARGE(B1:B50,ROW(INDIRECT("1:"&E1)))) // E1 chứa số lượng
=SUM(SMALL(B1:B50,ROW(INDIRECT("1:"&E1))))Nhập công thức mảng bằng Ctrl + Shift + Enter.
Tính tổng một số lượng biến đổi của các giá trị lớn nhất / nhỏ nhất bằng cách tham chiếu đến một ô
Tính Tổng Ô Tương Ứng Với Ô Trống/Không Trống
- Ô trống hoàn toàn:
=SUMIF(A2:A10,"=",C2:C10) - Ô chứa chuỗi rỗng:
=SUMIF(A2:A10,"",C2:C10) - Ô không trống:
=SUMIF(B2:B10,"",C2:D10)
Tính tổng các ô tương ứng với ô trống
SUM IF not blank
Sử dụng Hàm SUMIF với Ngày Tháng
SUMIF cũng có thể được sử dụng với điều kiện ngày tháng. Ví dụ:
- Ngày cụ thể:
=SUMIF(B2:B9,"10/29/2014",C2:C9) - Lớn hơn hoặc bằng ngày:
=SUMIF(B2:B9,">=10/29/2014",C2:C9) - Lớn hơn ngày trong ô khác:
=SUMIF(B2:B9,">"&F1,C2:C9) - Ngày hiện tại:
=SUMIF(B2:B9,TODAY(),C2:C9) - Trong vòng 7 ngày tới:
=SUMIF(B2:B9,"<"&TODAY()+7,C2:C9)
Tính tổng các giá trị tương ứng với ngày trong một tuần
Tính Tổng Giá Trị trong Khoảng Thời Gian
Sử dụng hiệu của hai hàm SUMIF:
=SUMIF(B2:B9,">=10/1/2014",C2:C9)-SUMIF(B2:B9,">=11/1/2014",C2:C9)
Công thức SUMIF để cộng các giá trị trong một khoảng thời gian nhất định
Tính Tổng Giá Trị trong Nhiều Cột
Có thể sử dụng cột phụ hoặc kết hợp nhiều hàm SUMIF. Ví dụ với cột phụ F chứa tổng của C, D, E:
=SUMIF(A2:A9,"táo",F2:F9)Hoặc không dùng cột phụ:
=SUM(SUMIF(A2:A9,"táo",C2:C9),SUMIF(A2:A9,"táo",D2:D9),SUMIF(A2:A9,"táo",E2:E9))
Dữ liệu nguồn cần tính tổng có điều kiện trong nhiều cột
Giải pháp để tính tổng giá trị trong nhiều cột
Kết Luận
Bài viết đã trình bày chi tiết về cách sử dụng hàm SUMIF trong Excel, hy vọng sẽ giúp bạn áp dụng hiệu quả vào công việc. Hãy luyện tập với các ví dụ để nắm vững kiến thức và khám phá thêm nhiều ứng dụng hữu ích của hàm SUMIF.
Discussion about this post