# Add cells containing certain text and numbers

#### acura123

##### Board Regular
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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Is all your data in 1 column?

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

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

This formula did't work

All the data is in the same column

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
ABCD
1
22 applesapples12
3grapes 6grapes6
4apples 10
56 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.

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"

Anyone have any ideas?

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
ABCDE
1
22 apples2apples12
3grapes 66grapes6
4apples 1010pears6
56 green pears6
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)

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,

Replies
3
Views
708
Replies
14
Views
515
Replies
7
Views
237
Replies
0
Views
233
Replies
6
Views
172

1,196,189
Messages
6,013,938
Members
441,796
Latest member
kmag

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back