Khắc phục lỗi #VALUE! khi dùng hàm SUM khi có lẫn ký tự

Chào mọi người,

Dạo này mình làm báo cáo thấy hay bị lỗi #VALUE! khi dùng hàm SUM, mà kiểm tra mãi không hiểu sao. Sau một hồi mò mẫm thì mới phát hiện ra là do trong cột số liệu của mình có lẫn một vài ô chứa ký tự (ví dụ: dấu chấm câu, hoặc một ký tự đặc biệt nào đó mà mình vô tình nhập vào).

Khi SUM gặp phải ô chứa ký tự này, nó sẽ trả về lỗi #VALUE! cho toàn bộ phép tính. Điều này rất khó chịu, đặc biệt là với các file số liệu lớn.

Hôm nay mình chia sẻ cách khắc phục đơn giản mà hiệu quả, dùng kết hợp SUMPRODUCT với ISNUMBER và VALUE.

Giả sử dữ liệu của bạn nằm ở vùng A1:A10, thay vì dùng công thức =SUM(A1:A10), bạn hãy thử công thức sau:

=SUMPRODUCT(VALUE(IF(ISNUMBER(A1:A10),A1:A10,0)))

Giải thích công thức:

• ISNUMBER(A1:A10): Kiểm tra xem từng ô trong vùng A1:A10 có phải là số hay không. Nó sẽ trả về mảng TRUE/FALSE.
• IF(ISNUMBER(A1:A10),A1:A10,0): Nếu ô đó là số thì giữ nguyên giá trị, nếu không phải số (chứa ký tự) thì thay bằng 0.
• VALUE(...): Chuyển đổi các giá trị (bao gồm cả những giá trị đã được thay thế bằng 0) về dạng số.
• SUMPRODUCT(...): Cộng tất cả các số trong mảng kết quả lại.

Cách này giúp bỏ qua các ô chứa ký tự mà không làm ảnh hưởng đến kết quả tính tổng. Hy vọng mẹo nhỏ này hữu ích cho các bạn!

Cảm ơn bạn đã chia sẻ kinh nghiệm xử lý lỗi #VALUE! khi dùng SUM. Đúng là gặp phải tình huống này với file lớn thì khá mệt mỏi thật.

Ngoài cách bạn chia sẻ, mình thấy dùng hàm AGGREGATE cũng rất hiệu quả trong trường hợp này. Hàm này cho phép ta bỏ qua các giá trị lỗi hoặc các hàng bị ẩn. Cụ thể, có thể dùng công thức như sau:

=AGGREGATE(9, 6, [Your_Range])

Trong đó, số 9 là để chỉ hàm SUM, và số 6 là để bỏ qua các giá trị lỗi. Không biết bạn đã thử cách này chưa?

Chào bạn, cảm ơn bạn đã chia sẻ một mẹo rất hay để xử lý lỗi #VALUE! khi có lẫn ký tự trong cột số liệu. Đúng là với những file Excel lớn, việc phát hiện và sửa từng ô bị lỗi rất tốn thời gian.

Mình cũng thường gặp tình huống này và có một cách làm khác cũng khá hiệu quả, đó là sử dụng kết hợp hàm SUMIF hoặc SUMIFS. Ví dụ, nếu bạn muốn tính tổng cột B mà bỏ qua các ô không phải số trong cột A, bạn có thể thử:

=SUMIF(A1:A100, ""&"", B1:B100)

Hoặc nếu bạn có nhiều điều kiện hơn:

=SUMIFS(B1:B100, A1:A100, ""&"", C1:C100, ">0")

Cách này sẽ chỉ tính tổng những ô ở cột B mà ô tương ứng ở cột A không trống (tức là có thể là số hoặc ký tự khác), và nếu cần thì kết hợp thêm điều kiện khác. Không biết bạn đã thử cách này chưa, có gì mình cùng trao đổi thêm nhé!

Đúng là lỗi #VALUE! khi có lẫn ký tự trong cột số liệu rất khó chịu thật bạn ơi. Cảm ơn bạn đã chia sẻ cách khắc phục!

Ngoài cách đó ra, mình cũng hay dùng kết hợp IFERROR với SUMPRODUCT để xử lý tình huống này. Ví dụ, nếu bạn muốn tính tổng cột A mà trong đó có lẫn ký tự, bạn có thể thử:

=SUMPRODUCT(IFERROR(--A1:A100,0))

Cách này sẽ ép các giá trị không phải số về 0 trước khi cộng. Không biết bạn đã thử cách này chưa, có gì mình cùng trao đổi thêm nha!