Bạn đang tìm cách tính tổng trong Excel có điều kiện để phục vụ công việc kế toán, bán hàng hay quản lý dữ liệu? Bài viết này sẽ hướng dẫn chi tiết cách sử dụng các hàm SUMIF, SUMIFS và công thức nâng cao để tính tổng theo điều kiện cụ thể – từ doanh số, lương thưởng đến tồn kho và chi phí. Đi kèm là file Excel mẫu thực hành và gợi ý môi trường làm việc tối ưu cho dân văn phòng hiện đại.
Danh sách
Cách tính tổng trong Excel có điều kiện
Trong nhiều tình huống thực tế, việc chỉ dùng hàm SUM đơn thuần là chưa đủ để giải quyết yêu cầu tính tổng dữ liệu có điều kiện. Đó là lý do vì sao cách tính tổng trong Excel có điều kiện trở thành công cụ thiết yếu cho dân văn phòng.
Thay vì cộng toàn bộ một cột số, bạn có thể chỉ tính tổng theo một điều kiện cụ thể – ví dụ: chỉ tính doanh số của nhân viên A, chỉ cộng số lượng hàng còn tồn dưới 50, hoặc tổng lương của phòng Kế toán.
Ứng dụng thực tế cách tính tổng trong excel có điều kiện:
Hàm SUMIF công cụ tính tổng với 1 điều kiện
=SUMIF(range, criteria, [sum_range])
Giải thích:
range: Vùng chứa điều kiện để so sánhcriteria: Điều kiện cần áp dụng (số, text, dấu so sánh…)sum_range: Vùng dữ liệu cần tính tổng (có thể khác range)2. Ví dụ minh họa cách tính tổng trong excel có điều kiện:
Bảng doanh số nhân viên:
| Nhân viên | Khu vực | Doanh số |
|---|---|---|
| An | Bắc | 15,000 |
| Bình | Nam | 20,000 |
| An | Nam | 18,000 |
| Cường | Bắc | 12,000 |
Yêu cầu: Tính tổng doanh số của nhân viên tên An
Công thức hàm tính tổng có nhiều điều kiện:
=SUMIF(A2:A5, "An", C2:C5) → Kết quả: 33,000
3. Mẹo sử dụng hàm tính tổng có nhiều điều kiện hiệu quả:
"A*" để đếm những tên bắt đầu bằng A">10000" hoặc "<50"">01/06/2024" sẽ hoạt động đúngVí dụ:
=SUMIF(C2:C10, ">01/06/2024", D2:D10) → Tổng đơn hàng sau ngày 1/6/2024
Hàm SUMIFS tính tổng với nhiều điều kiện cùng lúc
Khi bạn cần tính tổng dữ liệu với từ hai điều kiện trở lên, hàm SUMIF không còn đủ đáp ứng. Lúc này, hàm SUMIFS sẽ là công cụ lý tưởng giúp bạn xử lý nhanh gọn, chính xác.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Giải thích về hàm tính tổng theo điều kiện:
sum_range: Vùng cần tính tổngcriteria_range1: Vùng chứa điều kiện 1criteria1: Điều kiện 1criteria_range2: Vùng chứa điều kiện 2criteria2: Điều kiện 2Bảng dữ liệu:
| Nhân viên | Khu vực | Tháng | Doanh thu |
|---|---|---|---|
| An | Bắc | 6 | 15,000 |
| Bình | Nam | 6 | 20,000 |
| An | Bắc | 7 | 18,000 |
| Cường | Bắc | 6 | 12,000 |
Yêu cầu: Tính tổng doanh thu của khu vực Bắc trong tháng 6
Công thức:
=SUMIFS(D2:D5, B2:B5, "Bắc", C2:C5, 6) → Kết quả: 27,000
*3. Kết hợp với ký tự đại diện , ? trong điều kiện chuỗi:
* thay thế cho nhiều ký tự? thay thế cho một ký tựVí dụ: Tính tổng doanh thu nhân viên có tên bắt đầu bằng “A”:
=SUMIFS(D2:D5, A2:A5, "A*") → Lọc các dòng có nhân viên là An
Các hàm tính tổng có điều kiện khác trong Excel nên biết
Ngoài SUMIF và SUMIFS, Excel còn cung cấp nhiều công cụ khác để xử lý tổng theo điều kiện nâng cao, tùy thuộc vào độ phức tạp và cấu trúc dữ liệu cách tính tổng trong excel có điều kiện:
Khi bạn cần tính tổng với điều kiện linh hoạt, bạn có thể dùng:
=SUM(IF(logical_test, value_if_true, 0))
(Phải nhấn Ctrl + Shift + Enter nếu là mảng trong Excel cũ)
Ví dụ: Cộng doanh thu nếu khu vực là “Bắc” và tháng nhỏ hơn 7:
=SUM(IF((B2:B5="Bắc")*(C2:C5<7), D2:D5, 0))
Hàm DSUM áp dụng tốt khi làm việc với cơ sở dữ liệu dạng bảng (database) và cần lọc theo tiêu chí cụ thể.
Cú pháp:
=DSUM(database, field, criteria)
Ứng dụng cách tính tổng trong excel có điều kiện: Tính tổng doanh số dựa trên bảng điều kiện riêng biệt
Hàm AGGREGATE giúp bạn tính tổng mà chỉ cộng những dòng đang hiển thị sau khi lọc (filter).
Cú pháp:
=AGGREGATE(9, 5, range)
Ví dụ: =AGGREGATE(9, 5, D2:D20) → Tổng giá trị các dòng đang hiển thị
Ưu và nhược điểm các hàm cách tính tổng trong excel có điều kiện:
| Hàm | Ưu điểm | Nhược điểm |
|---|---|---|
| SUMIF | Dễ dùng, nhanh | Chỉ hỗ trợ 1 điều kiện |
| SUMIFS | Đa điều kiện, chính xác | Không dùng được với OR logic |
| IF+SUM | Linh hoạt nhiều điều kiện phức tạp | Cần biết công thức mảng |
| DSUM | Quản lý tốt với dữ liệu dạng bảng | Thiết lập tiêu chí hơi phức tạp |
| AGGREGATE | Hữu ích sau khi lọc dữ liệu | Không linh hoạt với nhiều điều kiện |
Cách xử lý lỗi khi dùng hàm tính tổng có điều kiện
Việc sử dụng các hàm tính tổng có điều kiện trong Excel như SUMIF, SUMIFS đôi khi gây ra lỗi hoặc cho ra kết quả không như mong muốn. Dưới đây là các lỗi phổ biến và cách xử lý các hàm tính tổng có điều kiện trong excel:
Ví dụ:
=IFERROR(SUMIF(A2:A100, "Tháng 13", B2:B100), 0)
→ Nếu không có “Tháng 13”, kết quả sẽ trả về 0 thay vì báo lỗi.
=SUMIFS(D2:D100, A2:A99, "Bắc") → vùng D2:D100 và A2:A99 khác độ dài → Lỗi!Mẹo các hàm tính tổng có điều kiện trong excel:
Áp dụng các hàm tính tổng có điều kiện trong Excel vào thực tế sẽ giúp bạn xử lý công việc nhanh chóng và chuyên nghiệp hơn:
Bảng ví dụ:
| Nhân viên | KPI đạt (%) | Thưởng |
|---|---|---|
| A | 110% | 3.000.000 |
| B | 95% | 0 |
| C | 120% | 5.000.000 |
Công thức:
=SUMIF(B2:B4, ">=100%", C2:C4)
→ Tính tổng thưởng cho nhân viên có KPI từ 100% trở lên.
| Mã đơn | Trạng thái | Giá trị |
|---|---|---|
| 001 | Đã giao | 2.000.000 |
| 002 | Chờ xử lý | 1.500.000 |
| 003 | Đã giao | 3.200.000 |
Công thức:
=SUMIF(B2:B4, "Đã giao", C2:C4)
→ Tính tổng doanh thu từ đơn hàng đã giao.
| Khoản chi | Tháng | Năm | Số tiền |
|---|---|---|---|
| Điện | 6 | 2024 | 1.200.000 |
| Nước | 6 | 2024 | 800.000 |
| Marketing | 7 | 2024 | 2.000.000 |
Công thức:
=SUMIFS(D2:D4, B2:B4, 6, C2:C4, 2024)
→ Tổng chi phí tháng 6 năm 2024.
| Mã hàng | Số lượng tồn | Mức tối thiểu |
|---|---|---|
| A001 | 5 | 10 |
| A002 | 15 | 10 |
| A003 | 8 | 10 |
Công thức:
=SUMIF(B2:B4, "<"&C2:C4, B2:B4)
Lưu ý: Công thức trên cần dùng công thức mảng hoặc cột phụ, vì SUMIF không xử lý trực tiếp so sánh giữa 2 vùng. Hoặc dùng:
=SUMPRODUCT((B2:B4<C2:C4)*B2:B4)
→ Tính tổng tồn kho dưới mức yêu cầu.
Nếu công việc hàng ngày của bạn gắn liền với Excel, báo cáo, dữ liệu, thì một môi trường làm việc ổn định, chuyên nghiệp và linh hoạt sẽ giúp tăng hiệu suất đáng kể. Và đó là lý do KingOffice trở thành lựa chọn lý tưởng cho cộng đồng văn phòng hiện đại.
Lý do chọn KingOffice:
Thông tin liên hệ KingOffice – Đối tác tin cậy của bạn:
