问题:

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")}

回答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))

回答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"}