• Status: Solved
• Priority: Medium
• Security: Public
• Views: 948

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
Zack
• 4
• 3
• 2
• +3
1 Solution

EngineerCommented:
Try something like

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

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

HTH,
Dan
0

Commented:
``````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

ConsultantCommented:
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

General IT Goto GuyAuthor Commented:
Thanks for the lighting response that why I love experts exchange.

http://www.rondebruin.nl/
0

ConsultantCommented:
Is this MVP Ron?
0

General IT Goto GuyAuthor Commented:
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

ConsultantCommented:
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

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

ConsultantCommented:
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

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

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.