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

Adding Kilobytes, Megabytes and Gigabytes Together on the 1 spreadsheet

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
Asked:
Zack
  • 4
  • 3
  • 2
  • +3
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Try something like

=SUMPRODUCT(LEFT(A1:A5,LEN(A1:A5)-2)*1000^(MATCH(LEFT(RIGHT(A1:A5,2)),{"K","M","G"},0)))/1000000
0
 
Dan CraciunIT ConsultantCommented:
Convert everything to a common unit (maybe bytes) into a new column, then add them.

HTH,
Dan
0
 
MacroShadowCommented:
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

Open in new window

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
zorvek (Kevin Jones)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
 
ZackGeneral IT Goto GuyAuthor Commented:
Thanks for the lighting response that why I love experts exchange.

http://www.rondebruin.nl/
0
 
zorvek (Kevin Jones)ConsultantCommented:
Is this MVP Ron?
0
 
ZackGeneral 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
 
zorvek (Kevin Jones)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
 
Naresh PatelTraderCommented:
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
 
zorvek (Kevin Jones)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 ;-)

If you want more I suggest asking your own question.

Kevin
0
 
Naresh PatelTraderCommented:
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
 
Naresh PatelTraderCommented:
This Is My Question Link.

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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now