How to Sum Weight Values Like “12 KG” or “105 KG” in Excel – 4 Formula Comparison

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(...)-3 removes 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:

👉 Watch now on YouTube

---

🔗 Stay Connected:

🌐 Website: https://quickinfoz.com
📺 YouTube: QuickInfoz Channel

---