|
» Summing Values from a List Containing Numerical and Textual Substrings
CATEGORY: Summing |
VERSIONS: All Microsoft Excel Versions |
|
Problem: 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))} |
| Screenshot // Summing Values from a List Containing Numerical and Textual Substrings |
  |
|
RATING:
2.22
VIEWS: 25377 |
||||
oldchippy wrote on January 12, 2007 04:43 EST
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 wrote on January 12, 2007 08:36 EST
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 wrote on December 31, 1969 19:00 EST
=VALUE(LEFT(B2, LEN(B2)-2)) and sum it all
Sponsors:
 |
Book Store:
Recommended Books:
- Millionaire Real Estate Mentor : The Secrets of Financial Freedom through Real Estate Investing
- The One Page Business Plan: Start With a Vision, Build a Company!
- Essentials of Accounting and Post Test Booklet 8, Eighth Edition
- The Guide to Understanding Financial Statements
- Special Edition Using Microsoft Outlook 2002
- Analysis of Financial Statements
Related MS EXCEL TIPS:
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
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