Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
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
=SUMPRODUCT(LEFT(A1:A5,LEN(A1:A5)-2) *1024^(MAT CH(LEFT(RI GHT(A1:A5, 2)),{"K"," M","G","T" },0)))/102 4^3
I will leave you with a valuable tip: plug the formula in and use Excel's Evaluate Formula function to see how it works ;-)
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.
=SUMPRODUCT(LEFT(A1:A5,LEN