Excel: Dynamic formula for change unit size in KB,

Question

How to correct the formula to be dynamic for all units KB, MB, GB, TB, etc ... It works perfectly for MB and GB but does not work for other units. my formula is:

{=IF(SUM(IF(ISNUMBER(VALUE(LEFT(A1:A10,LEN(A1:A10)-3))+0),VALUE(LEFT(A1:A10,LEN(A1:A10)-3))+0))>=1000,TEXT(SUM(IF(ISNUMBER(VALUE(LEFT(A1:A10,LEN(A1:A10)-3))+0),VALUE(LEFT(A1:A10,LEN(A1:A10)-3))+0))/1024,"0.00")&" GB",TEXT(SUM(IF(ISNUMBER(VALUE(LEFT(A1:A10,LEN(A1:A10)-3))+0),VALUE(LEFT(A1:A10,LEN(A1:A10)-3))+0)),"0.00")&" MB")}

Answer 1

USE:

=ROUND(SUMPRODUCT(LEFT(A1:A4,FIND(" ",A1:A4)-1)*10^(MATCH(RIGHT(A1:A4,2),{"KB","MB","GB","TB"},0)*3))/(10^(INT((LEN(SUMPRODUCT(LEFT(A1:A4,FIND(" ",A1:A4)-1)*10^(MATCH(RIGHT(A1:A4,2),{"KB","MB","GB","TB"},0)*3)))-1)/3)*3)),2) & " " & INDEX({"KB","MB","GB","TB"},INT((LEN(SUMPRODUCT(LEFT(A1:A4,FIND(" ",A1:A4)-1)*10^(MATCH(RIGHT(A1:A4,2),{"KB","MB","GB","TB"},0)*3)))-1)/3))

Answer 2

I did it like this and it's working fine for me.

{=TEXT(SUM(NUMBERVALUE(LEFT(A1:A4,FIND(" ",A1:A4,1)-1))*IF(RIGHT(A1:A4,2)="KB",1/1000000,IF(RIGHT(A1:A4,2)="MB",1/1000,IF(RIGHT(A1:A4,2)="GB",1,IF(RIGHT(A1:A4,2)="TB",1000,IF(RIGHT(A1:A4,2)="PB",1000000,0)))))),"0,00") & " GB"}