Adding Kilobytes, Megabytes and Gigabytes Together on the 1 spreadsheet

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

HTH,
Dan

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

Select allOpen in new window

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

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

http://www.rondebruin.nl/

Is this MVP Ron?

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

My apologies.

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

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

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

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

This Is My Question Link.

Thanks