microsoft excel - How to sum a column with cells containing text?

I have a a column that contains data size values, with numbers and varying text. Some have values like "3.54 TB" and some like "620 GB". Ultimately I want to get the total data size of a given column. For example, 3.54 TB + 620 GB = 4.16 TB.

If I could send the sum of cells containing "TB" to one cell, sum the cells containing "GB" to another cell - divide this GB cell by 1000, I can easily add those two cells.

Are there any formulas to sum a column of cells containing text? I do not want to download a plug-in.

Someone asked this here: How can I sum a column with cells containing text?

And salvo provided the formula below, but I return a "too few arguments" error when I use it...

=SUM(NUMBERVALUE(MID(A1:A6000,SEARCH("p",A1:A6000)-1)))

Can someone help?

Answer

Use this that iterates the cells and test the ending converting it all to TB and adding them together:

=SUMPRODUCT(LEFT(A1:A2,FIND(" ",A1:A2&" ")-1)/1000^(MATCH(RIGHT(A1:A2,2),{"TB","GB","MB","KB"},0)-1)) & " TB"

As it iterates LEFT(A1:A2,FIND(" ",A1:A2&" ")-1) returns the numeric part.

MATCH(RIGHT(A1:A2,2),{"TB","GB","MB","KB"},0)-1 returns 0,1,2,3 depending on where the text part is found in the array {"TB","GB","MB","KB"}

1000^ returns 1,1000,1000000,100000000000 depending on the return of the MATCH.