Tom’s Tutorials For Excel: Summing Numbers When Combined With Text

Tom’s Tutorials For Excel: Summing Numbers When Combined With Text

Because you are reading this, chances are you’re the go-to person in your workplace who’s counted on to work miracles in Excel.

In the picture, an ill-advised data entry practice shows a quantity of 3 production items, but with the numbers in the same cells as the text items. Your task is to add up all the numbers in column B, individually for Widgets, Winches, and Wombats.

Rather than go through the gyrations of Text To Columns and a few OR nested functions copied down for each production item, a single array formula for each item can do the job quickly and easily.

In yellow cell D3 for Widgets, the formula is:
=SUM(IF(SUBSTITUTE(B4:B39,D2,"")<>B4:B39,SUBSTITUTE(B4:B39,D2,"")+0))&" "&D2

In green cell E3 for Winches, the formula is:
=SUM(IF(SUBSTITUTE(B4:B39,E2,"")<>B4:B39,SUBSTITUTE(B4:B39,E2,"")+0))&" "&E2

In blue cell F3 for Wombats, the formula is:
=SUM(IF(SUBSTITUTE(B4:B39,F2,"")<>B4:B39,SUBSTITUTE(B4:B39,F2,"")+0))&" "&F2

Recall, array formulas are committed to the cell by simultaneously pressing the Ctrl+Shift+Enter keys. The curly braces are not entered by you; Excel places them automatically when the array formula is properly entered.