Sum a range of number end with “k”

Does it sound familiar to you?

Excel Tip - Sum with Text

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)

Excel Tip - Sum with Text 1

  • 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.

Excel Tip - Sum with Text 2

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

Excel Tip - Sum with Text 3.0

  • 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!

Excel Tip - Sum with Text 3

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?🙂

This entry was posted in Excel Tips, Formula and tagged , , , . Bookmark the permalink.

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

  1. 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

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

ExcelUser Blog

Work smarter by Mastering Functions in Excel

PowerPivotPro

Transforming your Business with Power Pivot and Power BI

Excel Esquire

Helping lawyers make the most of Microsoft Excel

ASAP Utilities for Excel – Blog

Work smarter by Mastering Functions in Excel

The Analyst Cave | Excel, VBA, programming and more

Work smarter by Mastering Functions in Excel

ExcelNova

zum Excel Profi werden

Ann's Blog

Equipping you to collect, analyze, and visualize data

The Ken Puls (Excelguru) Blog

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

Spreadsheet Page Blog

Work smarter by Mastering Functions in Excel

Oz du Soleil

Excel Training, Education & Entertainment

Peltier Tech Blog

Peltier Tech Excel Charts and Programming Blog

ExcelFort

Microsoft Excel Solutions and Training

Let's Talk Excel

Q&A about Excel

yoursumbuddy

Doug Glancy's Excel Site

Option Explicit VBA

A blog of Excel, Dashboards, Visual Basic for Applications, Data Analysis, Operations Research, and Visualizations.

EXCELXOR

Advanced Excel Techniques with XOR LX

Excel Unplugged

Collection of Excel Know How and little Excel Tips and Tricks

Excel and UDF Performance Stuff

Charles Williams on 'Making Excel go Faster'

Contextures Blog

Save Time and Money in Business

wmfexcel

Work smarter by Mastering Functions in Excel

Bacon Bits

Work smarter by Mastering Functions in Excel

Chandoo.org - Learn Excel & Charting Online

Fresh Excel Tips, Tricks, Charts, Tutorials, Downloads, Dashboards and Visualization Showcase for your Inspiration and Productivity

%d bloggers like this: