## Sum a range of number end with “k”

Does it sound familiar to you?

If you are having this kind of issue all the time, you may want to continue reading this as I am going to show you three different approaches to fix the problem.

1. Non-Formula approach
2. Formula approach
3. Fix the root approach

### 1. Non-Formula approach (if you can change the original data)

• Select the range (B2:B5) in the example
• Press Ctrl H to open “Find and Replace”
• Find what: k
• Replace with:  (nothing; just leave it blank)
• Replace All
• Now all “k” are removed and your data returns to real number, i.e. SUM should work as you expect.

Just to remind to put “(k)” in the header label so that you readers know the figures are in thousand.

### 2. Formula approach (if you prefer not to change original data at all)

#### 2.1) By using helper column

• In C2, input the following formula and then copy down.
`=SUBSTITUTE(B2,"k","")+0 `

SUBSTITUTE(text, old_text, new_text, [instance_num])

It simply means “In the text,  look for the old text we specify and then substitute it with the new text we want.”  In our example, it looks into “1.5k“, looks for “k“, and replaces it with nothing.  It returns “1.5” as a result.

SUBSTITUTE always returns text, the latter part of the formula “+0” converts the result (number stored as text) to a real number.

Now the numbers are ready for SUM. 🙂

#### 2.2) By using array formula

I heard that, you don’t want a helper column; and you don’t want to alter the original data either.  Here we go!

In B6, input the following formula

` =SUMPRODUCT(SUBSTITUTE(B2:B5,"k","")+0)&"k"`

The SUBSTITUTE function performs exactly the same as illustrated in helper column.  Instead of feeding a single cell reference, a range (B2:B5) is fed into it.

It returns an array of {“1.5” ; “2.3” ; “4.0” ; “12.8”}.

+0 converts the array into a number {1.5;2.3;4;12.8}

SUMPRODUCT then do the simple job to add the values {1.5;2.3;4;12.8} in the array, giving 20.6 as a result.

The final step is to concatenate the “k” to the number for consistent presentation. However this act converts the final result back to text.

Tip:

As SUMPRODUCT accepts array, it is used here for those who is afraid of Ctrl Shift Enter.  You may replace SUMPRODUCT with SUM; but you have to confirm the formula by Ctrl Shift Enter.

### 3. Fix the root approach (my preferred approach)

To prevent all these unnecessary tedious steps from happening, call the person who prepares the data, teach him/her how to format numbers into thousand in a proper way.

Don’t you think this is the best approach? 🙂

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips, Formula and tagged , , , . Bookmark the permalink.

### 5 Responses to Sum a range of number end with “k”

1. ywwp says:

Go to number format, and in custom, update 00 k, and then it works fine… the cell shows value with k appended. but do make the calculation where needed.

Like

• MF says:

That’s true when the value input is numeric. However in case of value being input as Text, changing format won’t work.

Liked by 1 person

• ywwp says:

yeah if it is text then it wont work

Like

2. Jim says:

your “consistent formatting” by using &”k” is not quite there if the answer is a whole number (ie it would not end in “.0k”)
to avoid this, use TEXT(formula,”0.0k”) around your formula

but I wholeheartedly agree with you: option 3 is by far the best (‘cos next time, you’ll get a mixture of “k” and “K” – to which the even-more-cumbersome solution is to add LOWER(…) around your range before SUBSTITUTE)

Muggles! – don’t you just love ’em?

Like

• MF says:

Hi Jim,
You are right about the decimal issue. Thanks for your suggestion for using TEXT which works great in the example.
Can’t agree more with you on the “k” and “K” issue. It could be even worse in real life – with a mixture of “nothing”, “K” and “M”. @_@
Cheers,

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Alluring Analytics

A Power BI Creator Blog

wmfexcel

Work smarter by Mastering Functions in Excel

Exploring open roads without breaking the bank

Work smarter by Mastering Functions in Excel

DataChant

Your next stop in mastering Power Query and Power BI

ExcelUser Blog

Work smarter by Mastering Functions in Excel

Leading Consultants on Power BI and the Microsoft Data Platform

Excel Esquire

Helping lawyers make the most of Microsoft Excel

ASAP Utilities Blog

Work smarter by Mastering Functions in Excel

Analyst Cave

Work smarter by Mastering Functions in Excel

The Excelguru Blog

More geeky stuff from the author of www.excelguru.ca...

Work smarter by Mastering Functions in Excel

Peltier Tech

Peltier Technical Services - Excel Charts and Programming

ExcelFort