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

How do I sum cells with text and numbers in Excel

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

1612767584272.png



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

Thank you.M

maabadi

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

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

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.

Video liên quan