When you’re working with weights like "12 KG" in Excel, =SUM() won’t work because these are text values. In this post, we’ll walk through 4 common formulas and explain which one you should use — and why.
Here’s the test data from our Excel table:
| Weight | |
|---|---|
| 1 | 12 KG |
| 2 | 18 KG |
| 3 | 9 KG |
| 4 | 15 KG |
| 5 | 105 KG |
🔢 Formula 1: =SUM(A4:A7)
❌ Result: 0
Why it fails: Excel treats text like “12 KG” as a non-numeric string. SUM() ignores text completely.
🔢 Formula 2: =SUM(--LEFT(C4:C7,2))
✅ Result: 54
Why it works (partially):
- This extracts the first two characters from each cell.
- Works for numbers like “12”, “18”, “09”, “15”
- ❌ Fails for three-digit numbers like “105 KG” — it only picks “10”, not “105”
- ✅ Ignores extra spaces after “KG” without issue.
🔢 Formula 3: =SUMPRODUCT(--LEFT(E4:E7,LEN(E4:E7)-3))
✅ Result: 144
Why it works:
LEN(...)-3removes exactly 3 characters from the end (" KG").- Correctly handles both 2-digit and 3-digit numbers
- ❌ May fail if there are more than 1 space after the unit — e.g.,
"105 KG "(with 2+ spaces)
🔢 Formula 4: =SUMPRODUCT(--LEFT(G4:G7,LEN(G4:G7)-3))
✅ Result: 144
Why it still works:
- Even with extra spaces after
"KG", Excel ignores trailing spaces in many text functions. - This version is more flexible in real-world messy data.
- ✅ Supports 3-digit and 2-digit weights
- ✅ Handles variations like
"105 KG "or"12 KG "
🧠 Conclusion
| Formula | Handles Text? | 3-Digit Safe? | Extra Spaces Safe? | Recommended |
|---|---|---|---|---|
SUM(A4:A7) | ❌ No | ❌ No | ✅ Yes | ❌ No |
SUM(--LEFT(...,2)) | ✅ Yes | ❌ No | ✅ Yes | ⚠️ Limited |
SUMPRODUCT(--LEFT(...,LEN()-3)) | ✅ Yes | ✅ Yes | ⚠️ Limited | ✅ Good |
SUMPRODUCT(...) with trailing spaces | ✅ Yes | ✅ Yes | ✅ Yes | ✅✅ Best |
🎬 Watch the Video:
🔗 Stay Connected:
🌐 Website: https://quickinfoz.com
📺 YouTube: QuickInfoz Channel