Gỡ rối lỗi #VALUE! khi dùng hàm SUM với vùng dữ liệu có chứa văn bản

Chào các bác, tình hình là em đang làm báo cáo trên Excel, mà gặp phải một lỗi khá khó chịu. Cụ thể là khi em dùng hàm SUM để cộng tổng một cột số liệu, thì nó lại trả về lỗi #VALUE!. Sau khi kiểm tra kỹ, em phát hiện ra trong cái cột đó có lẫn một vài ô chứa văn bản (ví dụ như 'N/A', 'Chưa có', hoặc đơn giản là dấu cách).

Hàm SUM mặc định sẽ không cộng các ô chứa văn bản, và nếu có ô nào bị lỗi (như #DIV/0!, #REF!...) thì nó cũng sẽ trả về #VALUE!.

Em đã thử vài cách nhưng chưa ưng ý lắm:

• Xóa thủ công các ô chứa văn bản: Tốn thời gian và dễ sót nếu file quá lớn.
• Dùng IFERROR bao ngoài hàm SUM: Vẫn trả về #VALUE! nếu có ô văn bản

Cuối cùng, em tìm ra cách dùng hàm SUMPRODUCT hoặc kết hợp SUM với IF để lọc bỏ các ô không phải số. Cách này khá hiệu quả:

Sử dụng SUMPRODUCT:

=SUMPRODUCT(--ISNUMBER(A1:A100), A1:A100)

Trong đó:

• ISNUMBER(A1:A100) sẽ trả về một mảng TRUE/FALSE (TRUE nếu ô là số, FALSE nếu là văn bản hoặc lỗi).
• Dấu -- sẽ chuyển đổi TRUE/FALSE thành 1/0.
• SUMPRODUCT sẽ nhân mảng 1/0 với mảng giá trị và cộng lại.

Hoặc dùng SUM và IF:

=SUM(IF(ISNUMBER(A1:A100), A1:A100, 0))

Lưu ý: Công thức này là công thức mảng, bạn cần nhấn Ctrl + Shift + Enter sau khi nhập để Excel hiểu đúng.

Cách này giúp em xử lý gọn gàng mà không cần sửa thủ công. Chia sẻ lên đây để anh em nào gặp tình huống tương tự có thể tham khảo.

Có bác nào có cách nào khác hay hơn không ạ?

Chào bạn,

Mình hiểu vấn đề bạn đang gặp phải với hàm SUM và dữ liệu có lẫn văn bản. Lỗi #VALUE! là khá phổ biến trong trường hợp này.

Để giải quyết, bạn có thể thử dùng hàm SUMIF hoặc SUMIFS để chỉ cộng những ô thỏa mãn điều kiện là số. Ví dụ, nếu bạn muốn cộng cột A, bạn có thể dùng công thức:

``excel =SUMIF(A:A,">0") `

Công thức này sẽ chỉ cộng những ô trong cột A có giá trị lớn hơn 0 (tức là các số). Nếu bạn muốn loại trừ cả các ô có giá trị là 0, bạn có thể điều chỉnh lại điều kiện.

Ngoài ra, một cách khác là dùng SUMPRODUCT kết hợp với ISNUMBER` để lọc ra các ô chứa số trước khi cộng.

Chúc bạn xử lý thành công!