SUMIF cộng các ô thoả một điều kiện, SUMIFS cộng theo nhiều điều kiện, COUNTIF và COUNTIFS đếm số ô thoả điều kiện. Ví dụ tổng doanh thu của nhân viên Lan: =SUMIF(B:B, "Lan", D:D); tổng doanh thu của Lan trong tháng 3 ở khu vực miền Nam thì dùng SUMIFS; đếm có bao nhiêu đơn của Lan: =COUNTIF(B:B, "Lan").
Chỗ dễ nhầm nhất là thứ tự đối số của SUMIF và SUMIFS ngược nhau: SUMIF để vùng cần cộng ở cuối, SUMIFS để nó ở đầu. Thứ hai là cách viết điều kiện có dấu so sánh (lớn hơn, khác) và điều kiện lấy từ một ô. Bài dưới dùng một bảng bán hàng mẫu để đi qua tất cả.
Bảng dữ liệu mẫu dùng trong bài
Giả sử sheet BanHang có dữ liệu từ hàng 2 đến hàng 1000 với các cột: A là Ngày bán, B là Nhân viên, C là Khu vực (Bắc, Trung, Nam), D là Doanh thu (số, đơn vị đồng), E là Mặt hàng. Mỗi dòng là một đơn hàng. Đây là dạng bảng phổ biến nhất ở văn phòng: nhật ký giao dịch, mỗi dòng một sự kiện, không có dòng tổng xen giữa.
Trước khi viết công thức, kiểm tra ba điều. Cột Ngày phải là ngày thật chứ không phải chữ: chọn một ô, nếu thanh công thức hiện đúng ngày và ô căn phải là ổn. Cột Doanh thu phải là số: không có dấu chấm phân cách gõ tay kiểu "1.200.000" dạng chữ. Cột Nhân viên không có khoảng trắng thừa ở cuối tên — lỗi vô hình làm SUMIF bỏ sót.
Để báo cáo gọn, nên dựng một vùng tổng hợp riêng, ví dụ ở sheet BaoCao: cột A liệt kê tên nhân viên, ô B1 chọn tháng, các ô bên dưới là công thức. Điều kiện lấy từ ô chứ không gõ cứng trong công thức, để khi đổi tháng hay thêm người bạn không phải sửa công thức nào.
Cú pháp bốn hàm, đặt cạnh nhau cho khỏi nhầm
Nhìn kỹ vị trí của "vùng cộng" trong từng hàm. Đây là chỗ sai phổ biến nhất khi chuyển từ SUMIF sang SUMIFS.
| Hàm | Cú pháp | Ví dụ |
|---|---|---|
| SUMIF | =SUMIF(vùng_điều_kiện, điều_kiện, [vùng_cộng]) | =SUMIF(B2:B1000, "Lan", D2:D1000) |
| SUMIFS | =SUMIFS(vùng_cộng, vùng_đk1, đk1, vùng_đk2, đk2, …) | =SUMIFS(D2:D1000, B2:B1000, "Lan", C2:C1000, "Nam") |
| COUNTIF | =COUNTIF(vùng, điều_kiện) | =COUNTIF(C2:C1000, "Bắc") |
| COUNTIFS | =COUNTIFS(vùng1, đk1, vùng2, đk2, …) | =COUNTIFS(B2:B1000, "Lan", D2:D1000, ">=5000000") |
| AVERAGEIF(S) | Giống SUMIF(S) nhưng tính trung bình | =AVERAGEIFS(D2:D1000, C2:C1000, "Nam") |
Mẹo nhớ: SUMIFS nhận số cặp điều kiện không cố định, nên vùng cộng được đặt ở vị trí duy nhất không đổi là đầu tiên. Nếu bạn quen dùng SUMIFS cho cả trường hợp một điều kiện, sẽ không bao giờ phải nhớ hai thứ tự khác nhau. Các vùng trong một công thức phải cùng kích thước (cùng từ hàng 2 đến 1000), lệch là ra #VALUE!. Máy đặt định dạng Việt Nam thì thay dấu phẩy giữa các đối số bằng dấu chấm phẩy.
Viết điều kiện đúng: so sánh, lấy từ ô, ký tự đại diện
Điều kiện so sánh nằm trong ngoặc kép. Doanh thu từ 5 triệu trở lên: ">=5000000". Khác Bắc: "<>Bắc". Ô trống: "" (hai dấu ngoặc kép liền nhau); ô không trống: "<>".
Ghép dấu so sánh với ô bằng dấu &. Ngưỡng nằm ở ô G1 thì viết ">="&G1, không viết ">=G1" (Excel sẽ hiểu là chữ "G1"). Lấy tên nhân viên từ ô A2 của sheet báo cáo thì chỉ cần A2, không cần ngoặc kép.
Ký tự đại diện * và ?. Dấu * thay cho chuỗi bất kỳ, dấu ? thay cho đúng một ký tự. Cộng mọi mặt hàng có chữ "áo": "*áo*". Mã bắt đầu bằng HN: "HN*". Muốn tìm đúng dấu * hay ? thật thì đặt dấu ngã trước: "~*".
Không phân biệt hoa thường. "lan" và "Lan" được tính như nhau. Nhưng "Lan" và "Lan " (thừa khoảng trắng) là khác nhau — lý do số liệu báo cáo hụt mà không ai hiểu vì sao.
Điều kiện "hoặc". SUMIFS chỉ ghép các điều kiện theo kiểu "và". Muốn tổng của Bắc hoặc Trung, cộng hai SUMIF lại: =SUMIF(C2:C1000,"Bắc",D2:D1000)+SUMIF(C2:C1000,"Trung",D2:D1000), hoặc gọn hơn =SUM(SUMIF(C2:C1000,{"Bắc","Trung"},D2:D1000)).
Cộng theo tháng và theo khoảng ngày
Báo cáo theo tháng là yêu cầu gặp hằng tuần. Cách chắc chắn nhất là dùng SUMIFS với hai điều kiện ngày: lớn hơn hoặc bằng ngày đầu, và nhỏ hơn hoặc bằng ngày cuối.
- Ở sheet BaoCao, ô B1 gõ ngày đầu tháng, ví dụ 01/03/2026 (hoặc dùng =DATE(2026,3,1) để khỏi lệ thuộc kiểu ngày tháng của máy).
- Ô B2 tính ngày cuối tháng: =EOMONTH(B1, 0), định dạng ô kiểu ngày.
- Tổng doanh thu tháng của nhân viên ở A5: =SUMIFS(BanHang!$D$2:$D$1000, BanHang!$B$2:$B$1000, A5, BanHang!$A$2:$A$1000, ">="&$B$1, BanHang!$A$2:$A$1000, "<="&$B$2).
- Kéo công thức xuống cho các nhân viên khác. Đổi ngày ở B1 thành tháng khác, toàn bộ bảng tự tính lại.
- Kiểm tra: tổng của cột vừa tính phải bằng =SUMIFS cả tháng không lọc nhân viên. Lệch là có tên viết khác nhau hoặc dòng thiếu tên.
Nếu cột ngày có cả giờ phút (dữ liệu xuất từ phần mềm bán hàng), điều kiện "<="&B2 sẽ bỏ sót mọi đơn sau 0 giờ ngày cuối tháng. Khi đó dùng "<"&B2+1, tức nhỏ hơn 0 giờ ngày đầu tháng sau. Đây là lỗi kinh điển làm doanh thu ngày cuối tháng "biến mất".
COUNTIF trong các việc không phải đếm doanh thu
COUNTIF hay bị xem là hàm phụ, nhưng nó giải quyết được nhiều việc kiểm tra dữ liệu mà người làm văn phòng gặp hằng ngày.
- Tìm dòng trùng: cột phụ =COUNTIF($A$2:$A$500, A2), lọc các dòng có kết quả lớn hơn 1 là ra mã khách, số hoá đơn bị nhập hai lần.
- Kiểm tra danh sách A có trong danh sách B không: =COUNTIF(DanhSachB!A:A, A2)=0 trả TRUE nghĩa là không có. Ví dụ đối chiếu danh sách đăng ký với danh sách đã nộp tiền.
- Đếm số người đạt: =COUNTIF(D2:D60, ">=5") đếm học viên từ 5 điểm trở lên; chia cho =COUNT(D2:D60) ra tỷ lệ.
- Đếm ô có chữ, ô trống: =COUNTIF(E2:E200, "*") đếm ô chứa chữ; =COUNTBLANK(E2:E200) đếm ô trống để biết còn bao nhiêu dòng chưa điền.
- Chặn nhập trùng ngay từ đầu: chọn cột, vào Dữ liệu, Xác thực dữ liệu, chọn Tuỳ chỉnh và nhập =COUNTIF($A:$A, A1)=1. Ai gõ mã đã có sẽ bị Excel từ chối.
Bẫy với mã số dài hơn 15 chữ số (số thẻ, mã vạch dài): COUNTIF tự hiểu chuỗi toàn chữ số là số, mà Excel chỉ giữ 15 chữ số có nghĩa, nên hai mã chỉ khác nhau ở mấy số cuối bị đếm là trùng — kể cả khi ô đã lưu dạng chữ. Cách khắc phục là ghép thêm dấu sao để buộc so sánh như chữ: =COUNTIF($A$2:$A$500, A2&"*").
Khi nào nên chuyển sang PivotTable hay SUMPRODUCT
SUMIFS rất hợp với báo cáo có khung cố định: danh sách nhân viên biết trước, mẫu báo cáo gửi sếp hằng tháng giống nhau. Nhưng nếu bạn cần xoay dữ liệu theo nhiều chiều — hôm nay theo khu vực, mai theo mặt hàng, tuần sau theo quý — viết hàng chục công thức SUMIFS sẽ mệt và dễ sai. Lúc đó PivotTable làm cùng việc chỉ bằng kéo thả, và tự cập nhật danh mục khi có nhân viên mới.
Ngược lại, khi điều kiện phức tạp hơn khả năng của SUMIFS, ví dụ cộng doanh thu các đơn trong tháng 3 bất kể năm, hãy dùng SUMPRODUCT: =SUMPRODUCT((MONTH(A2:A1000)=3)*D2:D1000). Biểu thức trong ngoặc trả về 1 hoặc 0 cho từng dòng, nhân với doanh thu rồi cộng lại. SUMPRODUCT chạy trên mọi bản Excel nhưng tính chậm hơn trên bảng rất lớn.
Dù dùng hàm nào, hãy luôn có một ô kiểm tra tổng: tổng các phần chia nhỏ phải bằng tổng chung của cả bảng. Ô này giúp bạn phát hiện ngay dòng thiếu tên, khu vực gõ sai chính tả hay ngày nằm ngoài phạm vi, trước khi con số đến tay người khác.
Mẫu điều kiện hay dùng, chép là chạy
Bảng dưới gom các điều kiện người làm văn phòng hay cần nhất, áp dụng được cho cả SUMIF, SUMIFS, COUNTIF, COUNTIFS và AVERAGEIF. Giả sử ngưỡng nằm ở ô G1 khi cần.
| Muốn lọc | Viết điều kiện | Ghi chú |
|---|---|---|
| Đúng bằng một chữ | "Nam" | Không phân biệt hoa thường |
| Đúng bằng giá trị ở ô | G1 | Không đặt trong ngoặc kép |
| Lớn hơn hoặc bằng ngưỡng ở ô | ">="&G1 | Dấu so sánh trong ngoặc, nối ô bằng & |
| Khác một giá trị | "<>Hủy" | Ví dụ bỏ đơn đã huỷ |
| Ô không trống | "<>" | Đếm ô đã nhập |
| Ô trống | "" | Hoặc dùng COUNTBLANK |
| Bắt đầu bằng | "HN*" | Mã HN01, HN-Q1… |
| Chứa một chữ ở ô G1 | "*"&G1&"*" | Tìm theo một phần tên |
| Từ hôm nay trở đi | ">="&TODAY() | Hạn chót, lịch hẹn sắp tới |
Điều kiện ngày nên luôn ghép với hàm DATE hoặc một ô chứa ngày thật, không gõ chữ kiểu ">=01/03/2026", vì cách Excel đọc chuỗi ngày phụ thuộc định dạng ngày của từng máy: tệp chạy đúng trên máy bạn có thể sai trên máy đồng nghiệp đặt kiểu tháng trước ngày sau.
Câu hỏi thường gặp
SUMIF và SUMIFS khác nhau thế nào?
SUMIF chỉ nhận một điều kiện và vùng cần cộng đặt ở cuối. SUMIFS nhận từ một đến nhiều điều kiện, tất cả phải đúng cùng lúc, và vùng cần cộng đặt ở đầu. SUMIFS làm được mọi việc của SUMIF, nên nhiều người chỉ dùng SUMIFS cho thống nhất.
Vì sao SUMIF trả về 0 dù chắc chắn có dữ liệu?
Kiểm tra cột cần cộng có phải số thật không, vì số lưu dạng chữ sẽ bị bỏ qua. Tiếp đó kiểm tra điều kiện có khớp từng ký tự, nhất là khoảng trắng thừa ở cuối tên. Cuối cùng xem có lỡ đảo vị trí vùng điều kiện và vùng cộng khi chuyển giữa SUMIF và SUMIFS không.
Làm sao đếm số giá trị không trùng lặp trong một cột?
Trên Microsoft 365 hoặc Excel 2021, dùng =COUNTA(UNIQUE(A2:A500)) cho nhanh. Bản cũ hơn có thể dùng =SUMPRODUCT(1/COUNTIF(A2:A500,A2:A500)), với điều kiện vùng không có ô trống. Một cách không cần công thức là đưa cột vào PivotTable và đếm số dòng nhãn.
COUNTIF có đếm theo màu ô được không?
Không. Các hàm đếm và cộng của Excel không đọc màu nền. Nếu màu thể hiện trạng thái như đã thanh toán, nên thêm một cột trạng thái bằng chữ rồi COUNTIF theo cột đó. Có thể lọc theo màu bằng bộ lọc rồi dùng SUBTOTAL để xem số dòng đang hiện.
SUMIFS có dùng được trong Google Sheets không?
Có, SUMIF, SUMIFS, COUNTIF, COUNTIFS và AVERAGEIFS trong Google Sheets có cú pháp giống Excel. Khác biệt chủ yếu là dấu phân cách đối số tuỳ cài đặt ngôn ngữ và vùng của bảng tính, xem trong Tệp, Cài đặt. Google Sheets còn có hàm QUERY cho các báo cáo phức tạp hơn.
Có thể dùng SUMIF với điều kiện là một phần của chữ không?
Có, dùng ký tự đại diện. Ví dụ cộng doanh thu mọi mặt hàng có chữ bánh ở bất kỳ vị trí nào, điều kiện viết là dấu sao, chữ bánh, dấu sao, đặt trong ngoặc kép. Nếu chữ cần tìm nằm trong ô G1, ghép điều kiện bằng dấu sao nối với G1 bằng dấu và.
