Add cells containing certain text and numbers

[acura123]

I want to be able to add the numerical values of cells containing a certain word. Lets say Apples

For Example:

Data

2 apples

grapes 6

apples 10

6 pears


Thanks

Answer: Number of Apples: 14

 

[orbea_adam]

Is all your data in 1 column?

Also...14? Is that "new math"?

 

[Aladin Akyurek]

=SUMPRODUCT((0&TRIM(SUBSTITUTE(UPPER(A2:A5),"APPLES","")))+0)

 

[acura123]

This formula did't work

All the data is in the same column

 

[acura123]

Soory about the math mistake

 

[Aladin Akyurek]

This formula did't work

All the data is in the same column

My fault. Try not to describe the issue you have with a formula with "didn't work" for it does not help to diagnose the problem, although I think you had a #VALUE! error with that formula.

Here is remedy...

Book13
	A	B	C	D
1
2	2 apples		apples	12
3	grapes 6		grapes	6
4	apples 10
5	6 pears
Sheet1

D2:

=SUM(IF(ISNUMBER((0&TRIM(SUBSTITUTE(UPPER(A2:A5),UPPER(C2),"")))+0),(0&TRIM(SUBSTITUTE(UPPER(A2:A5),UPPER(C2),"")))+0,0))

which is confirmed with control+shift+enter (not just with enter) then copied down.

 

[acura123]

The count I am getting is 0

I think because the words contain other words in front of them and behind them.

EX. 6 Green Pears
Fancy Pears 5

Where the refrence cell is "pears"

 

[acura123]

Anyone have any ideas?

 

[Aladin Akyurek]

The count I am getting is 0

I think because the words contain other words in front of them and behind them.

EX. 6 Green Pears
Fancy Pears 5

Where the refrence cell is "pears"

Obviously...

Options:

1] Separate text from figures and then do the required calculations.
2] Sollicite code in VBA if you don't want to go with [1].
3] Download and install the morefunc.xll add-in in order to have a set up like below if you don't want to follow either [1] or [2]...

aaCondSumCellsWithTextAndNumbers acura123.xls
	A	B	C	D	E
1
2	2 apples	2		apples	12
3	grapes 6	6		grapes	6
4	apples 10	10		pears	6
5	6 green pears	6
6
Sheet1

B2:

=MCONCAT(IF(ISNUMBER(SETV(--MID(A2,INTVECTOR(LEN(A2),1,1),1))),GETV(),""))+0

which is confirmed with control+shift+enter (not just with enter) then copied down.

E2, copied down:

=SUMIF($A$2:$A$5,"*"&D2&"*",$B$2:$B$5)

 

[Anthony47]

In the example you gave (6 Green Pears, Fancy Pears 5) do you wish to calculate separately the number of Green Pears and of Fancy Pears, or just you wish to calculate 11 Pears? In this second case, do you have a list of items that you wish to count, in wich format?

Bye,