|  

» Summing Values from a List Containing Numerical and Textual Substrings

CATEGORY - Summing VERSION - 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
Summing Values from a List Containing Numerical and Textual Substrings


Rate This Tip
12 34 5
Rating: 2.28     Views: 26898
date calculation in Excel
zilfar@yahoo.com
I tried to do date calculation in excel but seems like couldnt get the result which I want.I extracted data from SAP with a standard date being 12.02.2004 when I paste it into the Excel and tried to do the calculation eg. 12.12.2005 - 12.02.2004 in Excel.it gives me and error message.

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
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
Click here to post comment
For Registered Users
Name
Comment Title
Comments