Excel Magic Trick 1222: Formula To Add 10kg, 6kg, 19kg: Array Formula Or Custom Number Format?

Learn how to Create a Formula to Add Column Filled With Weights Like: 10kg, 33kg, 19kg:
1. (00:10) Problem Setup
2. (01:12) Customer Number Formatting to show Number with the units “kg”. Custom Number Format to Display units such as “kg” for Number Data.
3. (02:45) Array Formula that uses the Functions SUBSTITUTE and SUMPRODUCT. Extract Numbers from Alpha Numeric data with SUBSTITUTE. Add array with SUMPRODUCT.

SUM function does not add text. How to add column filled with cells that contain numbers and text. How do I add numbers with text units in cells?

[kerryking]

I never cease to be amazed at the capabilities of XL and thank you Mike for demonstrating the almost never ending possibilities with your video series.

[Reduce_Scan]

Thank you
and this is another solution

[LotfyKozman]

Good محمد حلمى.Also, Split column in Power Query can do the job, in addition text to column can also help.

[SyedMuzammilMahasanShahi]

That is beautiful, simple n EXCELlent. Thanks Mike.

[learitee]

Great trick Mike, I appreciate the work-around :)

[melonnelon]

I love you😭😭..now let me go ace my exam

[lonebrukjr]

This is so great, thanks! Can I ask how you do this with numbers with a hyphen between them. For example 1234-1234kg

[michaelconnors]

Since the text argument in the Substitute function can handle an array, the text argument in the left, right mid and len functions should also handle arrays as well.  This will also return 1307: =SUMPRODUCT(LEFT(A2:A31, LEN(A2:A31)-2)+0)

[donek]

Hi there, In minute 1.50 there is talk about the format cell, can we put more text with kg, for example if I got in the same column grams, pounds and ounces . So can I use this formula and write 0"kg, grams, ounces"

[raoul]

Here's my (dumb) solution: 1) Select column 2) Find and Replace kg with nothing 3) Custom Format cells as 0"kg" as per your video, now cells can be SUMmed. Are there some drawbacks of this solution?


Edit: Actually I think I will change to using sumproduct and substitution, primarily because they can be used without changing the original data (always my preference). Cheers

[Kazbb]

Hi! how would you count the cells in a column for anything over for example 20kg? I can't have helper columns to disregard the "kg" ☹

[lvtbus]

Hello, have a similar problem. Have a kids attendance sheet with financial data in it.
ck, cs, cc stands for check, cash, credit card.
every other line has attendance data from a drop menu, 1 to 3 letter combinations, so should not interfere.
Example:
ck44
WX
cc44
X
ck13
FC
cs77

So now I would like it to calculate sum for
CK as 57
CC as 44
CS as 77

also since I need to count how many checks I carry to the bank I got a countif for the amount of physical checks i have, here it's 2.

As an added bonus, is it possible to count the CK if I write ck13 + ck13 in one cell and it counts it as 26 and countifs as 2 checks?
Thank you soooo much!
RD

[nasirmirzaey]

When have deferent taxt, which famulla use?

[AnthonyBB]

How can you do it with non adjacent cells?

[sahidulahmed]

how to put that Kg in superscript format in the same cell?

[PrashantPatil-wibm]

sir quintal.kilo.gram add subbtrac multiply which formate use (100.23.78-230.56.89)

[juliotrujillo]

How to add lb and oz (13 Lb 5 oz + 7 lb 8 oz to give a total 20 lb 13 oz

[bosem]

hello there, i there anyway to protect the table with ability to add row with tab click at end of the table .. thanks teacher

[narithsan]

I can't download any file file from the link above, any suggestion?

[gjl]

I have a question, I want to make a table with locations and quantity but my table has to be something like this:
location : 1B001A thru 1B024A with quantity 4 in each location and then another column from 1B001B thru 1B024B with quantity 7 in each location, etc., the list goes more than 1000 entries, but I don't want to type all of them, is there an easy way of doing this?
Thanks in advance