» 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:
Recommended Books:
- Personal Finance for Dummies
- Microsoft Outlook 2002 for Dummies
- Microsoft Word Version 2002 Inside Out
- The McGraw-Hill Guide to Writing a High-Impact Business Plan: A Proven Blueprint for First-Time Entrepreneurs
- Lower Your Taxes - Big Time! : Wealth-Building, Tax Reduction Secrets from an IRS Insider
- Microsoft Excel VBA Programming for the Absolute Beginner
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