Sum Cells Containing Text And Numbers

[pg24]

Hi,

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.

 

[maabadi]

Try this:

Book1
	A	B	C	D	E	F
1	Ruler	Calculators	Pens & Books	Comment
2			40	40 Pens		200
3			20	20 Pens
4	60
5			40	20 Pens, 20 Books
6		40
7			40	40 Pens
8			20	20 Pens
9			20	20 Pens
10			40	40 Pens
Sheet1

Cell Formulas
Range	Formula
F2	F2	=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]

This would allow for Pens even if not listed first in the cell.

21 02 08.xlsm
	D	E	F
1	Comment		Pens
2	40 Pens		200
3	20 Pens
4	60 Rulers
5	20 Pens, 20 Books
6	40 Calculators
7	3 Calculators, 40 Pens
8	20 Pens
9	20 Pens
10	1 calculator, 40 Pens, 3 Rulers
Pens

Cell Formulas
Range	Formula
F2	F2	=SUM(--RIGHT(SUBSTITUTE(TRIM(IFERROR(LEFT(D2:D10,SEARCH(F1,D2:D10)-1),0))," ",REPT(" ",20)),20))

 

[pg24]

Thank you guys, worked like a charm!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.