Ask Your Question

How to do a column sum when the cells are not numeric formatted

asked 2018-03-08 11:13:12 +0200

velizet gravatar image

Hi, I want to do a column sum from an imported calc document about data usage. The data in the cells are like "1.38 MB", "870 Bytes", "25.6 KB" etc. Any help will be appreciated. Thanks for answers.

edit retag flag offensive close merge delete


This is not a matter of the format set for the cells, but one of the recognition of text appended to the numeric part of the entries as a modifyier to the meaning.
There is no ready-made tool for the task.

Lupp gravatar imageLupp ( 2018-03-08 13:19:04 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-03-08 12:35:06 +0200

Lupp gravatar image

updated 2018-03-08 12:36:29 +0200

To sum numeric data provided in text form is not a problem:
Assuming the range A2:A1001 {=SUM(IFERROR(VALUE(A2:A1001);"NotNumeric"))} entered for array-evaluation will do.

The factual task is therefor the implied one: How can I convert the the text parts looking like units with SI-like prefixes to numeric factors?

There recently was this thread addressing a related question concerning input.
In your case you first should replace the wrongly used SI prefixes to the correct ones: M by Mi, K by Ki ...
How to do it is explained in detail in this attached example. I did not try to cobble a formula for a one-cell-solution. However splitting over so many helper columns may seem too detailed as soon as the solution is understood.

edit flag offensive delete link more


Thanks Lupp

velizet gravatar imagevelizet ( 2018-03-09 08:40:26 +0200 )edit

@velizet: If this answer was helpful then please mark it as correct. See guidelines for asking.

Jim K gravatar imageJim K ( 2018-03-13 15:29:16 +0200 )edit

answered 2018-03-12 18:24:28 +0200

updated 2018-03-13 09:26:05 +0200

No Macro's just formula's! Will sum cells with # with or without a decimal and with or without text(s) at right of #'s. 1gb, 1g, 1 gb, 1.11gb etc. C:\fakepath\Sum Numbers having text at right.ods

edit flag offensive delete link more


You have enough karma to attach a file here, edit your question an use the 'clip' icon to attach a file.

m.a.riosv gravatar imagem.a.riosv ( 2018-03-12 23:46:36 +0200 )edit

Using regular expressions can be done with only one formula =SUMPRODUCT(VALUE(LEFT($O$7:$O$22;SEARCH("([^0-9\.\,]|$)";$O$7:$O$22;1)-1))), maybe your file was an excel file it has wildcards enable, not regular expressions, and formula is local seeting dependant..

m.a.riosv gravatar imagem.a.riosv ( 2018-03-13 02:19:11 +0200 )edit

Thanks @m.a.riosv! I spent a lot of time trying to put a link on it! Didn't know I could attach, having tried if before, (with less karma)!

Mark McLean gravatar imageMark McLean ( 2018-03-13 09:31:12 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-03-08 11:13:12 +0200

Seen: 211 times

Last updated: Mar 13 '18