Hàm SUMIFS, COUNTIFS trong Excel: tính theo nhiều điều kiện
Cách dùng SUMIFS, COUNTIFS, AVERAGEIFS trong Excel để tính tổng, đếm, tính trung bình theo nhiều điều kiện như tháng, nhân viên, khu vực, kèm lỗi hay gặp.
“Tổng doanh số của nhân viên Lan ở khu vực Hà Nội trong tháng 10 là bao nhiêu?” — câu hỏi kiểu này gặp hằng ngày trong công việc văn phòng. Thay vì lọc dữ liệu rồi cộng tay, bộ ba hàm SUMIFS, COUNTIFS và AVERAGEIFS trả lời ngay trong một ô và tự cập nhật khi dữ liệu thay đổi.
1. Dữ liệu mẫu
Giả sử bảng bán hàng có các cột: A Ngày, B Nhân viên, C Khu vực, D Sản phẩm, E Số lượng, F Thành tiền, dữ liệu từ dòng 2 đến dòng 500.
,. Nếu máy bạn đặt định dạng vùng Việt Nam, Excel có thể yêu cầu dấu chấm phẩy ; — ví dụ =SUMIFS(F2:F500;B2:B500;"Lan").2. SUMIFS — tính tổng theo nhiều điều kiện
=SUMIFS(vùng_tính_tổng, vùng_điều_kiện_1, điều_kiện_1, vùng_điều_kiện_2, điều_kiện_2, ...)Lưu ý: khác với SUMIF cũ, ở SUMIFS vùng cần cộng đứng đầu tiên.
| Câu hỏi | Công thức |
|---|---|
| Tổng tiền của Lan | =SUMIFS(F2:F500, B2:B500, "Lan") |
| Lan bán ở Hà Nội | =SUMIFS(F2:F500, B2:B500, "Lan", C2:C500, "Hà Nội") |
| Đơn từ 10 triệu trở lên | =SUMIFS(F2:F500, F2:F500, ">=10000000") |
| Sản phẩm có chữ “iPhone” | =SUMIFS(F2:F500, D2:D500, "*iPhone*") |
| Khác khu vực Hà Nội | =SUMIFS(F2:F500, C2:C500, "<>Hà Nội") |
Điều kiện lấy từ ô khác
Đừng gõ cứng tên người vào công thức. Đặt tên nhân viên ở ô H2, khu vực ở I2, rồi viết =SUMIFS($F$2:$F$500, $B$2:$B$500, H2, $C$2:$C$500, I2). Kéo công thức xuống là có ngay bảng tổng hợp cho cả nhóm. Khi so sánh với ô, phải nối toán tử bằng dấu &: ">="&K1.
3. Tính theo khoảng thời gian (theo tháng)
Ngày bắt đầu đặt ở K1 (01/10/2026), ngày kết thúc ở K2 (31/10/2026):
=SUMIFS(F2:F500, A2:A500, ">="&K1, A2:A500, "<="&K2)Cùng một cột A được dùng hai lần cho hai điều kiện “từ ngày” và “đến ngày”. Mẹo: thay K2 bằng EOMONTH(K1,0) để luôn lấy ngày cuối tháng — xem thêm các hàm ngày tháng.
4. COUNTIFS — đếm theo nhiều điều kiện
=COUNTIFS(vùng_1, điều_kiện_1, vùng_2, điều_kiện_2, ...)- Số đơn của Lan ở Hà Nội:
=COUNTIFS(B2:B500, "Lan", C2:C500, "Hà Nội") - Số đơn trên 10 triệu:
=COUNTIFS(F2:F500, ">10000000") - Số ô đã có dữ liệu ở cột trạng thái:
=COUNTIFS(G2:G500, "<>") - Kiểm tra mã bị trùng:
=COUNTIFS($D$2:$D$500, D2)>1trả về TRUE nếu mã ở D2 xuất hiện nhiều lần.
5. AVERAGEIFS — trung bình theo điều kiện
Cú pháp giống SUMIFS: =AVERAGEIFS(F2:F500, C2:C500, "Đà Nẵng") cho giá trị đơn trung bình ở Đà Nẵng. Nếu không có dòng nào thoả điều kiện, hàm trả lỗi #DIV/0!; bọc lại bằng =IFERROR(AVERAGEIFS(...), 0).
6. Lỗi hay gặp và cách sửa
| Hiện tượng | Nguyên nhân |
|---|---|
| Kết quả bằng 0 dù chắc chắn có dữ liệu | Có khoảng trắng thừa (“Lan ”), số hoặc ngày lưu dạng chữ. Dùng TRIM và kiểm tra lại kiểu dữ liệu — xem làm sạch dữ liệu. |
#VALUE! | Các vùng không cùng kích thước, ví dụ F2:F500 đi với B2:B499. |
| Kéo công thức bị lệch vùng | Quên dấu $ để cố định vùng dữ liệu. |
| Điều kiện ngày không chạy | Viết ">=K1" trong ngoặc kép — Excel hiểu là chữ “K1”. Phải viết ">="&K1. |