# How do I sum cells with text and numbers in Excel

Ppg24New MemberJoinedFeb 8, 2021Messages2Office Version365PlatformWindowsFeb 8, 2021#1Hi,I was hoping someone can help me with a way to calculate amount of Pens onlyThe SUMIF formu

P

#### pg24

##### New MemberJoinedFeb 8, 2021Messages2Office Version
1. 365Platform
2. Windows
• Feb 8, 2021
• #1Hi,

I was hoping someone can help me with a way to calculate amount of Pens only

The SUMIF formula counts the cell with Books as well, would there be a way to exclude "Books" in the calculation?

Thank you.M

##### Well-known MemberJoinedOct 22, 2012Messages2,681Office Version
1. 2019
2. 2016Platform
3. Windows
• Feb 8, 2021
• #2Try this:
Book1ABCDEF1RulerCalculatorsPens & BooksComment24040 Pens20032020 Pens46054020 Pens, 20 Books64074040 Pens82020 Pens92020 Pens104040 PensSheet1Cell FormulasRangeFormulaF2F2=SUMPRODUCT(IF(ISNUMBER(LEFT(\$D\$2:\$D\$10,2)*1),LEFT(\$D\$2:\$D\$10,2)*1,0)*(MID(\$D\$2:\$D\$10,4,4)="Pens"))Press CTRL+SHIFT+ENTER to enter array formulas.

#### Peter_SSs

##### MrExcel MVP, ModeratorJoinedMay 28, 2005Messages54,579Office Version
1. 365Platform
2. Windows
• Feb 8, 2021
• #3This would allow for Pens even if not listed first in the cell.

21 02 08.xlsmDEF1CommentPens240 Pens200320 Pens460 Rulers520 Pens, 20 Books640 Calculators73 Calculators, 40 Pens820 Pens920 Pens101 calculator, 40 Pens, 3 RulersPensCell FormulasRangeFormulaF2F2=SUM(--RIGHT(SUBSTITUTE(TRIM(IFERROR(LEFT(D2:D10,SEARCH(F1,D2:D10)-1),0))," ",REPT(" ",20)),20))

Reactions: HabtestSolutionP

#### pg24

##### New MemberJoinedFeb 8, 2021Messages2Office Version
1. 365Platform
2. Windows
• Feb 8, 2021
• #4Thank you guys, worked like a charm!

#### Peter_SSs

##### MrExcel MVP, ModeratorJoinedMay 28, 2005Messages54,579Office Version
1. 365Platform
2. Windows
• Feb 8, 2021
• #5You're welcome. Glad we could help. Thanks for the follow-up.