Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

Posted on 2014-03-19
Medium Priority
802 Views
Heyas,

If have spreadsheet with values like this in 1 particular column.

50MB
60 KB
6.1 GB
60KB (Yes Spacing does vary)
223MB

How do I do add these entries together to get the answer in MB. I tried looking into the convert function but I don't think it gives me what I need.

Thank you.
0
Question by:Zack
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2
• +3

LVL 43

Accepted Solution

Saqib Husain, Syed earned 2000 total points
ID: 39941486
Try something like

=SUMPRODUCT(LEFT(A1:A5,LEN(A1:A5)-2)*1000^(MATCH(LEFT(RIGHT(A1:A5,2)),{"K","M","G"},0)))/1000000
0

LVL 35

Expert Comment

ID: 39941490
Convert everything to a common unit (maybe bytes) into a new column, then add them.

HTH,
Dan
0

LVL 27

Expert Comment

ID: 39941495
``````Function DemoFunction() As Double

Dim cel As Range
Dim dblGB As Double, dblMB As Double, dblKB As Double

For Each cel In Range("D1:A5").Cells
Select Case Right(cel, 2)
Case Is = "GB"
dblGB = dblGB + Left(cel, Len(cel) - 2)
Case Is = "MB"
dblMB = dblMB + Left(cel, Len(cel) - 2)
Case Is = "KB"
dblKB = dblKB + Left(cel, Len(cel) - 2)
End Select
Next cel

DemoFunction = (dblGB * 1024) + (dblKB / 1024) + dblMB

End Function
``````
0

LVL 81

Expert Comment

ID: 39941499
Here is a more accurate version of the SUMPRODUCT formula:

=SUMPRODUCT(LEFT(A1:A5,LEN(A1:A5)-2)*1024^(MATCH(LEFT(RIGHT(A1:A5,2)),{"K","M","G","T"},0)))/1024^3

And a more accurate UDF:

Public Function SumStorage(ByVal Source As Range) As Double

Dim Cell As Range
Dim Result As Double
Dim Position As Long

For Each Cell In Source.Cells
Position = 0
Do: Position = Position + 1: Loop Until Mid(Cell.Value, Position, 1) Like "[A-Za-z ]"
Select Case True
Case InStr(UCase(Cell.Value), "TB") > 0: Result = Result + CDbl(Left(Cell.Value, Position - 1)) * 1024 * 1024 * 1024 * 1024
Case InStr(UCase(Cell.Value), "GB") > 0: Result = Result + CDbl(Left(Cell.Value, Position - 1)) * 1024 * 1024 * 1024
Case InStr(UCase(Cell.Value), "MB") > 0: Result = Result + CDbl(Left(Cell.Value, Position - 1)) * 1024 * 1024
Case InStr(UCase(Cell.Value), "KB") > 0: Result = Result + CDbl(Left(Cell.Value, Position - 1)) * 1024
End Select
Next Cell
SumStorage = Result

End Function

To use the UDF, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

Use this formula:

=SumStorage(A1:A5)

The formula and UDF return KB. To return TB:

=SumStorage(A1:A5)/1024^3

Kevin
0

Author Closing Comment

ID: 39941500
Thanks for the lighting response that why I love experts exchange.

http://www.rondebruin.nl/
0

LVL 81

Expert Comment

ID: 39941501
Is this MVP Ron?
0

Author Comment

ID: 39941509
No it isn't :( that was meant to be messaged to a friend of mine who want some examples on excel lists.

My apologies.
0

LVL 81

Expert Comment

ID: 39941515
No worries. I was a little surprised because Ron (the Microsoft Excel MVP) is rather capable of solving such a riddle on his own ;-)
0

LVL 8

Expert Comment

ID: 39941517
Mr. zorvek (Kelvin Jones),

Surfing EE & I show your solution. will you please Explain the formula? if you have bit of spare time.
=SUMPRODUCT(LEFT(A1:A5,LEN(A1:A5)-2)*1024^(MATCH(LEFT(RIGHT(A1:A5,2)),{"K","M","G","T"},0)))/1024^3

Thanks
0

LVL 81

Expert Comment

ID: 39941522
A few things.

My name is Kevin, not Kelvin.

It is not considered good etiquette to hijack another OP's question.

I will leave you with a valuable tip: plug the formula in and use Excel's Evaluate Formula function to see how it works ;-)

Kevin
0

LVL 8

Expert Comment

ID: 39941532
Opps Typo mistake Sorry,

I will leave you with a valuable tip: plug the formula in and use Excel's Evaluate Formula function to see how it works ;-)

Thumbs Up

Thank You
0

LVL 8

Expert Comment

ID: 39941542

Thanks
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This lâ€¦
###### Suggested Courses
Course of the Month11 days, 14 hours left to enroll