Mẹo nhỏ: Xử lý lỗi #VALUE! khi dùng SUM với ô chứa văn bản hoặc ô trống

Chào các bạn trong diễn đàn Sheet & Excel!

Mình thường xuyên làm việc với Excel và hôm nay gặp một vấn đề khá phổ biến nhưng đôi khi gây khó chịu: lỗi #VALUE! khi sử dụng hàm SUM. Cụ thể là khi trong vùng dữ liệu cần tính tổng có chứa các ô trống hoặc các ô chứa văn bản (không phải số).

Thông thường, hàm SUM sẽ báo lỗi #VALUE! nếu nó gặp phải một ô không phải là số trong dải tính toán. Điều này làm ảnh hưởng đến kết quả cuối cùng.

Sau một hồi tìm tòi, mình đã tìm ra một cách rất đơn giản để khắc phục tình trạng này mà không cần phải thêm các hàm phức tạp như IFERROR hay AGGREGATE (mặc dù các hàm đó cũng rất hay).

Cách mình hay dùng là kết hợp hàm SUM với hàm SUMPRODUCT. Nghe có vẻ lạ nhưng nó hoạt động rất hiệu quả.

Giả sử bạn có dữ liệu từ ô A1 đến A10, và trong đó có thể có ô trống hoặc văn bản. Thay vì chỉ dùng:

=SUM(A1:A10)

Bạn hãy thử công thức sau:

=SUMPRODUCT(N(A1:A10))

Hoặc nếu bạn muốn nó linh hoạt hơn và vẫn dùng SUM:

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

Lưu ý: Công thức SUM(IF(...)) này là một công thức mảng, bạn cần nhấn Ctrl + Shift + Enter sau khi nhập để nó hoạt động đúng trên các phiên bản Excel cũ hơn. Trên Excel 365 thì chỉ cần Enter là được.

Hàm N() sẽ chuyển đổi các giá trị không phải số (như văn bản, ô trống, lỗi) thành số 0, còn các số thì giữ nguyên. Do đó, SUMPRODUCT sẽ tính tổng các giá trị số mà không bị lỗi.

Cách này giúp mình xử lý nhanh các file dữ liệu

Chào bạn,

Vấn đề bạn gặp phải với hàm SUM và các ô chứa văn bản/trống là rất quen thuộc. Ngoài cách bạn đang tìm hiểu, mình thường dùng hàm SUMIF hoặc SUMIFS để giải quyết tình huống này. Ví dụ, nếu bạn muốn tính tổng cột A, bỏ qua các ô không phải số, có thể dùng:

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

Hoặc nếu muốn chắc chắn hơn, có thể kết hợp với ISNUMBER:

=SUMPRODUCT(N(ISNUMBER(A:A))*A:A)

Bạn thử xem sao nhé!

Cảm ơn bạn đã chia sẻ mẹo xử lý lỗi #VALUE! với hàm SUM. Cách của bạn rất hay và dễ áp dụng!

Mình cũng xin góp thêm một cách khác, cũng khá hiệu quả, đó là sử dụng hàm AGGREGATE. Hàm này có một ưu điểm là cho phép bỏ qua các lỗi, bao gồm cả lỗi #VALUE! mà không cần dùng thêm các hàm phụ trợ.

Ví dụ, để tính tổng cột A và bỏ qua các ô không phải số, bạn có thể dùng:

=AGGREGATE(9, 6, A:A)

Trong đó:

• 9 là mã hàm SUM.
• 6 là tùy chọn bỏ qua các lỗi (bao gồm #VALUE!).
• A:A là dải ô cần tính tổng.

Bạn thử xem cách này có tiện lợi hơn không nhé!