SUMIFS cộng các giá trị thoả mãn đồng thời nhiều điều kiện; COUNTIFS đếm số dòng thoả mãn đồng thời nhiều điều kiện. Đây là hai hàm được dùng nhiều nhất trong các bảng tổng hợp ở văn phòng: doanh thu theo nhân viên và tháng, số đơn theo trạng thái, số ngày công theo phòng ban. Chúng có mặt ở mọi phiên bản Excel từ 2007, cả Google Sheets, nên tệp dùng chung không lo vấn đề tương thích.
Bài viết giải thích cú pháp và cách viết từng loại điều kiện, đi qua ví dụ giả định dựng bảng tổng hợp theo nhân viên và tháng bằng một công thức kéo ngang kéo dọc, xử lý điều kiện theo ngày, điều kiện "hoặc", và cách kiểm số tổng khi nhờ AI viết công thức.
SUMIFS khác SUMIF ở đâu và vì sao nên quen dùng SUMIFS
SUMIF với một điều kiện có cú pháp SUMIF(vùng điều kiện, điều kiện, vùng tính tổng), vùng tính tổng đứng cuối. SUMIFS với nhiều điều kiện thì đảo lại: 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, ...), vùng tính tổng đứng đầu. Sự đảo thứ tự này là nguồn lỗi kinh điển khi sửa từ SUMIF sang SUMIFS bằng tay, và cũng là chỗ AI đôi khi viết nhầm.
Lời khuyên thực tế là dùng SUMIFS cho cả trường hợp chỉ có một điều kiện. Bạn chỉ phải nhớ một thứ tự đối số, và khi cần thêm điều kiện thứ hai chỉ việc nối vào cuối. COUNTIFS và AVERAGEIFS theo cùng logic: COUNTIFS không có vùng tính tổng, chỉ có các cặp vùng điều kiện và điều kiện; AVERAGEIFS có vùng tính trung bình đứng đầu.
Một điểm cần hiểu đúng: mọi điều kiện trong SUMIFS và COUNTIFS được nối bằng "và". Một dòng chỉ được cộng khi thoả tất cả điều kiện cùng lúc. Nếu bạn cần "nhân viên A hoặc nhân viên B", một SUMIFS đơn thuần không làm được; phần sau của bài có cách xử lý. Ngoài ra, mọi vùng trong công thức phải có cùng kích thước, ví dụ cùng từ dòng 2 đến dòng 5000, lệch nhau là hàm báo #VALUE!.
Cuối cùng, SUMIFS chỉ cộng những ô chứa số thật. Ô trống, ô chữ, ô có công thức trả về chuỗi rỗng đều bị bỏ qua, không gây lỗi. Đặc điểm này vừa tiện vừa nguy hiểm: tiện vì cột tiền có vài ô ghi chú không làm hỏng công thức, nguy hiểm vì một loạt số tiền dán từ phần mềm khác ở dạng chữ cũng bị bỏ qua lặng lẽ.
Cách viết từng loại điều kiện
Điều kiện trong SUMIFS và COUNTIFS được viết dưới dạng chuỗi chữ, đây là chỗ người mới hay bối rối nhất. Bảng dưới liệt kê các dạng thường dùng, giả định ô J1 chứa giá trị điều kiện.
| Muốn lọc | Cách viết điều kiện | Ghi chú |
|---|---|---|
| Bằng một giá trị | J1 hoặc "Đã giao" | Không phân biệt chữ hoa thường |
| Lớn hơn hoặc bằng một số trong ô | ">="&J1 | Phép so sánh nằm trong ngoặc kép, nối với ô bằng dấu & |
| Khác một giá trị | "<>Huỷ" | Dùng để loại đơn huỷ khỏi tổng |
| Chứa một đoạn chữ | "*"&J1&"*" | Dấu * thay cho chuỗi bất kỳ, chỉ áp dụng với ô chứa chữ |
| Bắt đầu bằng | "HN*" | Ví dụ mọi mã đơn bắt đầu bằng HN |
| Ô trống | "" | Đếm cả ô có công thức trả chuỗi rỗng |
| Ô không trống | "<>" | Hay dùng để đếm số dòng đã nhập |
| Từ một ngày trở đi | ">="&DATE(2026,3,1) | Cột ngày phải là ngày thật, không phải chữ |
Chỗ dễ sai là viết phép so sánh và tham chiếu chung trong ngoặc kép, như ">=J1". Excel hiểu đó là so với chữ J1, không phải giá trị trong ô J1, và kết quả bằng 0 mà không báo lỗi. Khi đọc công thức AI đưa, hãy soát kỹ dấu ngoặc kép và dấu &.
Nếu dữ liệu thật có chứa dấu * hay dấu ? trong nội dung, ví dụ mã hàng có dấu sao, bạn cần đặt dấu ngã trước nó trong điều kiện để Excel hiểu là ký tự thường chứ không phải ký tự đại diện.
Ví dụ giả định: bảng tổng hợp doanh thu theo nhân viên và tháng
Một chuỗi nhà thuốc nhỏ ở Đà Nẵng có trang DuLieu ghi mỗi hoá đơn một dòng: cột A ngày bán (ngày thật), cột C tên nhân viên, cột E trạng thái, cột G thành tiền. Cần trang TongHop với tên nhân viên chạy dọc cột A từ ô A3 và ngày đầu mỗi tháng chạy ngang dòng 2 từ ô B2.
- Ở dòng 2, nhập ngày đầu tháng thật (1/1/2026, 1/2/2026...) rồi định dạng hiển thị dạng tháng năm. Dùng ngày thật thay vì chữ "Tháng 1" để công thức tính được khoảng ngày.
- Tại ô B3 gõ: =SUMIFS(DuLieu!$G:$G,DuLieu!$C:$C,$A3,DuLieu!$A:$A,">="&B$2,DuLieu!$A:$A,"<="&EOMONTH(B$2,0),DuLieu!$E:$E,"<>Huỷ").
- Chú ý dấu $ hỗn hợp: $A3 khoá cột để khi kéo ngang vẫn đọc tên nhân viên ở cột A; B$2 khoá dòng để khi kéo xuống vẫn đọc ngày ở dòng 2. Vùng dữ liệu khoá cả cột.
- Kéo công thức sang phải hết các tháng và xuống hết danh sách nhân viên. Một công thức duy nhất điền cả bảng.
- Thêm cột tổng cuối mỗi dòng và dòng tổng cuối mỗi cột bằng SUM, rồi so ô tổng cuối bảng với tổng cột G của các hoá đơn không huỷ trong cùng khoảng thời gian. Hai số phải bằng nhau.
- Muốn đếm số hoá đơn thay vì cộng tiền, dựng bảng thứ hai với cùng bố cục, thay SUMIFS bằng COUNTIFS và bỏ đối số vùng tính tổng.
Nếu ô tổng cuối bảng nhỏ hơn tổng thật, thủ phạm thường là tên nhân viên trong dữ liệu gõ khác với tên ở cột A, ví dụ thừa khoảng trắng hay khác dấu. Lọc cột C của DuLieu để xem danh sách tên thực tế, bạn sẽ thấy ngay những biến thể như "Lan" và "Lan " hay "Thuý" và "Thúy".
Bảng tổng hợp này là điểm dừng của khâu công thức. Đọc ý nghĩa xu hướng theo tháng, vẽ biểu đồ hay dựng bảng điều khiển là phần việc phân tích, bạn có thể tìm ở trang AI Phân Tích.
Điều kiện theo ngày tháng: dùng khoảng ngày, đừng dùng hàm MONTH
Yêu cầu "doanh thu tháng 3" rất phổ biến, và phản xạ đầu tiên của nhiều người, kể cả AI, là thử MONTH(A:A)=3 bên trong SUMIFS. Cách này không chạy: SUMIFS đòi vùng điều kiện phải là một vùng ô thật, không nhận một mảng tính ra từ hàm. Cách đúng là chuyển "tháng 3" thành hai điều kiện trên cùng cột ngày: lớn hơn hoặc bằng ngày 1/3 và nhỏ hơn hoặc bằng ngày cuối tháng 3, như ví dụ ở trên với EOMONTH. Cách này còn tự đúng cho cả tháng 2 năm nhuận mà bạn không phải nghĩ.
Nếu bạn thật sự cần lọc theo tháng mà không quan tâm năm, ví dụ so sánh tháng 3 của nhiều năm, có thể dùng SUMPRODUCT với MONTH, hoặc thêm cột phụ Tháng vào dữ liệu gốc rồi SUMIFS theo cột đó. Cột phụ dễ hiểu hơn cho người tiếp quản tệp, và cũng dễ kiểm hơn.
Bẫy lớn nhất với ngày là dữ liệu không phải ngày thật. Ngày xuất từ phần mềm bán hàng hay dán từ tin nhắn thường là chữ, và khi máy cài vùng Việt Nam còn hay lẫn giữa kiểu ngày tháng năm và tháng ngày năm. Ô ngày thật khi đổi định dạng sang General sẽ hiện thành một con số khoảng 46 nghìn với ngày trong năm 2026; ô vẫn giữ nguyên dạng chữ là ngày giả. Chuyển đổi các ô này thuộc khâu làm sạch dữ liệu, cần làm trước khi viết công thức.
Điều kiện "hoặc": ba cách xử lý
Cộng hai SUMIFS. Muốn tổng doanh thu của hai nhân viên, viết =SUMIFS(G:G,C:C,"Lan")+SUMIFS(G:G,C:C,"Minh"). Dễ đọc, chạy mọi phiên bản. Cách này đúng khi hai điều kiện "hoặc" nằm trên cùng một cột, vì một dòng không thể vừa là Lan vừa là Minh, nên không bao giờ bị cộng hai lần.
Dùng hằng mảng trong ngoặc nhọn. Dạng =SUM(SUMIFS(G:G,C:C,{"Lan","Minh"})) gọn hơn khi danh sách dài. Lưu ý dấu ngăn cách bên trong ngoặc nhọn cũng phụ thuộc cài đặt vùng của máy; nếu Excel báo lỗi khi gõ, hãy hỏi AI cách viết cho đúng vùng của bạn hoặc quay lại cách cộng từng SUMIFS.
Cẩn thận khi "hoặc" nằm trên hai cột khác nhau. Ví dụ "đơn của nhân viên Lan hoặc đơn giao ở Hội An". Một dòng có thể thoả cả hai, nếu cộng hai SUMIFS thì dòng đó bị cộng hai lần. Cách đúng là cộng hai SUMIFS rồi trừ đi SUMIFS của dòng thoả cả hai điều kiện, hoặc dùng SUMPRODUCT với điều kiện tổng lớn hơn 0. Đây là chỗ nên nhờ AI viết và kiểm trên dữ liệu mẫu có dòng trùng.
Danh sách điều kiện thay đổi thường xuyên. Nếu nhóm nhân viên cần cộng thay đổi theo tháng, đặt danh sách vào một vùng ô riêng và dùng SUMPRODUCT kết hợp COUNTIF, hoặc thêm cột phụ đánh dấu nhóm trong dữ liệu gốc. Sửa danh sách ở một chỗ, công thức không phải đụng tới.
Nhờ AI viết SUMIFS và kiểm lại con số tổng
SUMIFS là loại công thức AI viết đúng cú pháp rất thường xuyên, nhưng sai về ý nghĩa thì vẫn xảy ra, vì nó không biết dữ liệu của bạn. Cách làm việc hiệu quả:
- Mô tả từng cột theo đúng tên và kiểu dữ liệu, nói rõ cột ngày là ngày thật, cột tiền là số, có cột trạng thái nào cần loại trừ không.
- Nói rõ điều kiện lấy từ ô nào và công thức sẽ kéo theo chiều nào, để AI đặt dấu $ đúng.
- Yêu cầu AI liệt kê từng điều kiện bằng lời sau công thức. Bạn đọc phần lời để soát xem có điều kiện nào thừa hay thiếu.
- Kiểm bằng bộ lọc: lọc dữ liệu gốc đúng theo các điều kiện, chọn cột tiền, nhìn tổng hiện ở thanh trạng thái phía dưới cửa sổ Excel. Con số phải khớp với SUMIFS.
- Kiểm chéo bằng PivotTable dựng nhanh từ cùng dữ liệu. Nếu PivotTable và bảng SUMIFS lệch nhau, có điều kiện nào đó đang được hiểu khác nhau.
- Không dán dữ liệu thật như tên khách, số điện thoại, giá vốn vào chatbot; cấu trúc cột và vài dòng giả định là đủ.
Một lỗi lặng lẽ khác đáng kiểm: số tiền lưu dạng chữ. SUMIFS bỏ qua những ô này, không báo lỗi, và tổng của bạn bị hụt. Dùng COUNTIFS đếm số dòng thoả điều kiện, rồi so với số dòng thấy được khi lọc. Nếu đếm đủ mà tổng hụt, hãy kiểm cột tiền bằng ISNUMBER.
Câu hỏi thường gặp
SUMIFS có dùng được với điều kiện nằm ở trang tính khác không?
Được. Vùng tính tổng và vùng điều kiện có thể nằm ở trang tính khác, ô chứa giá trị điều kiện cũng vậy. Chỉ cần mọi vùng có cùng kích thước. Nếu vùng nằm ở một tệp khác, công thức vẫn chạy khi tệp đó đang mở, nhưng có thể báo lỗi khi tệp nguồn đóng, nên tốt nhất gom dữ liệu về cùng tệp.
Vì sao SUMIFS trả về 0 trong khi chắc chắn có dữ liệu?
Ba nguyên nhân hay gặp nhất là điều kiện viết sai dạng, ví dụ đặt cả tham chiếu ô trong ngoặc kép; giá trị điều kiện khác giá trị trong dữ liệu vì khoảng trắng thừa hoặc khác dấu; và cột ngày hay cột tiền đang lưu dạng chữ. Kiểm từng điều kiện bằng cách tạm bỏ bớt, xem tổng thay đổi ra sao.
COUNTIFS có đếm được số khách không trùng không?
Không trực tiếp. COUNTIFS đếm số dòng thoả điều kiện, khách mua ba lần được đếm ba lần. Để đếm khách không trùng, trên Microsoft 365 bạn có thể dùng UNIQUE kết hợp FILTER rồi đếm; trên bản cũ hơn có thể dùng công thức SUMPRODUCT với COUNTIFS hoặc PivotTable có tuỳ chọn đếm giá trị khác nhau.
Có nên dùng cả cột như G:G trong SUMIFS không?
Với SUMIFS, tham chiếu cả cột thường không làm chậm đáng kể vì Excel chỉ xét phần có dữ liệu, và có lợi là dữ liệu mới thêm vào tự được tính. Tuy vậy, nếu bên dưới bảng có dòng tổng hay ghi chú, chúng sẽ bị cộng lẫn. Dùng Table với tham chiếu theo tên cột là cách gọn và an toàn hơn.
AVERAGEIFS khác gì lấy SUMIFS chia COUNTIFS?
Về kết quả thường giống nhau, nhưng AVERAGEIFS báo lỗi chia cho 0 khi không có dòng nào thoả điều kiện, còn cách tự chia cho phép bạn kiểm soát trường hợp đó. Ngoài ra AVERAGEIFS bỏ qua ô trống trong vùng tính trung bình, trong khi COUNTIFS vẫn đếm dòng đó, nên hai cách có thể lệch khi dữ liệu thiếu.