Tip Printed from ExcelTip.com

---

Summing Values from a List Containing Numerical and Textual Substrings


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))}