Hoctin.vn đã có bài viết hướng dẫn sử dụng hàm SUMIF đơn giản và chi tiết nhất từ cơ bản đến nâng cao cho dữ liệu dạng số, văn bản, ngày tháng cùng các lưu ý quan trọng khi áp dụng hàm tổng có điều kiện.
Ở bài viết này, hoctin.vn sẽ tiếp tục giới thiệu với các bạn các ví dụ nâng cao của hàm SUMIF trong Excel với công thức mảng, kết hợp hàm logic và tính tổng các giá trị dựa trên ngày hiện thời.
Mục Lục Bài Viết
1, Sử Dụng Hàm SUMIF Trong Excel Rút Gọn Công Thức Tính Tổng Có Nhiều Điều Kiện Kết Hợp Hàm OR
Giả sử bạn có một danh sách các máy trong cột A, giá tiền tương ứng trong cột B.
Bạn có thể tính tổng giá trị của từng loại máy với công thức =SUMIF(A:A,“Tên máy”,B:B).
Tuy nhiên nếu sử dụng công thức SUMIF đơn giản trên, khi cần tính tổng giá trị của một danh sách gồm nhiều loại máy khác nhau trong cột A thì bạn cần phải gõ đi gõ lại công thức nhiều lần, ví dụ để tính tổng giá trị Máy hàn và Máy ép:
=SUMIF(A3:A7,”Máy hàn”,B3:B7)+SUMIF(A3:A7,”Máy ép”,B3:B7)
Với một danh sách vài trăm loại máy thì điều này tốn rất nhiều thời gian và công sức. Rất may là Excel có công thức mảng có thể hỗ trợ bạn:
=SUMPRODUCT(SUMIF(A3:A7,D3:D4,B3:B7))
Criteria của hàm SUMIF lúc này dạng dãy D3:D4 chứa 2 giá trị được sử dụng làm tiêu chí. Phạm vi này có thể được mở rộng để bao gồm các tiêu chí bổ sung khi cần thiết.
2, Sử Dụng Công Thức Mảng Để Tính Điều Kiện Hoặc (OR) Trong Trường Hợp Hàm SUMIF Không Hoạt Động Chính Xác Khi Kết Hợp Hàm Logic
Tính tổng có 2 điều kiện trở lên xảy ra cùng lúc thì rất đơn giản, chỉ cần sử dụng hàm SUMIFS.
Giả sử bạn có một danh sách các loại hoa quả trong cột A, hãng tương ứng trong cột B và Doanh thu (Sales) tương ứng trong cột C.
Bạn có thể tính tổng doanh thu của đáp ứng 2 điều kiện: là Táo và Hãng Zespri với công thức =SUMIFS(C3:C7,A3:A7,”táo”,B3:B7,”zespri”) như ví dụ dưới đây:
Nhưng trường hợp tính tổng nếu thỏa mãn 1 trong 2 điều kiện thì sao?
Nếu áp dụng đơn thuần hàm SUMIF kết hợp hàm OR có thể dẫn đến kết quả của biểu thức sai vì các giá trị thỏa mãn cả 2 điều kiện được cộng hai lần như hình dưới đây.
Bạn cần sử dụng công thức mảng trong trường hợp này:
=SUM(IF((A3:A7=”Táo”)+(B3:B7=”zespri”),1,0)*C3:C7)
Lưu ý: Ấn Ctrl + Shift+ Enter để công thức mảng hoạt động.
3, Dùng Hàm SUMIF Tính Tổng Các Giá Trị Phát Sinh Trong Một Khoảng Thời Gian Nhất Định
Làm thế nào để tính tổng các giá trị ứng phát sinh giữa hai ngày?
Bạn có thể sử dụng hàm SUMIFS cho phiên bản Excel từ 2007 trở đi.
Nếu phiên bản Excel của bạn không hỗ trợ công thức SUMIFS, hãy sử dụng hai công thức SUMIF để tính tổng các giá trị phát sinh giữa ngày T và ngày T+n như sau:
=SUMIF(range,“>=T”,[sum_range])-SUMIF(range,“>=T+n”,[sum_range])
4, Sử Dụng Hàm SUMIF Trong Excel Để Tính Tổng Giá Trị Hóa Đơn Dựa Trên Ngày Hiện Tại
Một ví dụ hay gặp đối với các bạn kế toán, đó là tính giá trị các hóa đơn phát sinh trong một số ngày gần nhất hoặc kiểm tra giá trị hóa đơn đến hạn thanh toán.
Giả sử bạn có một danh sách các hóa đơn trong cột A, ngày hóa đơn tương ứng trong cột B và Số tiền tương ứng trong cột C.
Bạn có thể tính tổng giá trị các hóa đơn phát sinh trong 30 ngày gần nhất bằng cách sử dụng hàm SUMIF kết hợp hàm TODAY() như ví dụ sau:
Hoặc có thể thêm trường Tuổi nợ bằng cách lấy Mốc ngày cần tính (cuối niên độ kế toán, cuối tháng hoặc ngày hôm nay bằng cách sử dụng hàm TODAY) trừ đi ngày hóa đơn và sử dụng hàm SUMIF như dưới đây:
Trên đây là các ví dụ hay về hàm SUMIF trong Excel. Hi vọng bài viết của hoctin.vn đã mang lại cho các bạn các thông tin hữu ích về cách sử dụng nâng cao của hàm SUMIF.
Ngoài ra, để ứng dụng hiệu quả Excel, bạn cần phải sử dụng tốt các hàm, các công cụ khác của Excel, hãy cùng đón đọc series bài viết ứng dụng các hàm Excel:
Hàm SUM và các cách tính tổng trong Excel
Hàm SUMIF từ cơ bản đến nâng cao