» Summing Values from a List Containing Numerical and Textual Substrings
CATEGORY - Summing
VERSION - All Microsoft Excel Versions
Listed in columns A & B are file names and their sizes, each of which is one of three types (KB, MB or GB).
We want to sum the totals for each file size type.
Solution:
Use the SUM, IF, ISNUMBER, FIND, VALUE, and LEFT functions as shown in the following Array formula:
{=SUM(IF(ISNUMBER(FIND(C2,B2:B5)),VALUE(LEFT(B2:B5,FIND(C2,B2:B5)-1)),0))}
Book Store:
oldchippy
OK the Tip works fine, but it would be more beneficial if the process of arriving with the answer could be explained in detail?
janssenchoy
Doesn't the "-2" require you to know in advance how long each of the letter sequences you're removing are? Answer is sorta misleading anyway, since after conversion, it should be around 1600 MB and change (since base 10 is only approximate).
Easier way to do this
Vinay
=VALUE(LEFT(B2, LEN(B2)-2)) and sum it all
How am I going to make sure that the substraction between these two dates will give me and answer in days.
Pls note that I am a layman in using Excel formulas.fyi i dont want to change the date to 12/02/05 instead I would prefer it to be as waht I originally received it.
Pls help.Thanks in advance.
Zilfar