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

 

[acura123]

I have a column of numbers and text like this

6 fancy pears
6 oranges
red pears 5
3 ripe pears

What I wanted to do is have a formula add up the numerical value in those cells containing the word "pears"

The answer of which should be : 14

 

[Anthony47]

Try this.
Lets suppose that your data are in A2 and below.
Then in cell B1 type Qty (for "quantity") and in B2 use =IF(ISERROR(LEFT(A2,2)+0),0,LEFT(A2,2)+0)+IF(ISERROR(RIGHT(A2,2)+0),0,RIGHT(A2,2)+0) and copy this formula down.
In C1 type the word you wish to count, for example Pears
In C2 use =IF(ISERROR(FIND(C$1,$A2)),0,B2)
Finally in D1 use =SUM(C:C) that should be the total you are looking for.

This should work if the quantity is either at the very beginning or very end of the text and is max 99.

Please note that my excel is not in English, there is a risk that the traslated formulas are in error; plaese try and lets us know.

Bye,

 

[acura123]

Very very close except the number it is extracting is sometimes in the middle of the cell

I need something that would say:

If the cell contains the word "pear" anywhere in the cell then add the number that is anywhere in that cell

Is there a one step process that is a simplier formula?

 

[Anthony47]

Well, in this scenario let's try this.
Open the VBA editor (ALT-F11), in the "Project" area (the frame at the left with the name of the workbook and its worksheet), select a "Module" and copy in it the following code:

Function qty(ByVal Texstring As String)
For I = 1 To Len(Texstring)
WW = Val(Mid(Texstring, I, 1))
If WW > 0 Then GoTo out
Next I
out:
qty = WW
If Val(Mid(Texstring, I, 2)) = 0 Then Exit Function
qty = Val(Mid(Texstring, I, 2))
If Val(Mid(Texstring, I, 3)) > 0 Then qty = Val(Mid(Texstring, I, 3))
End Function

If there is no any Module, just add one (Menu ->Insert ->Module and copy there the code.
Then in cell B2 use =qty(A2) and copy the formula down.
In C1 type the word you wish to count, for example Pears
In C2 use =IF(ISERROR(FIND(C$1,$A2)),0,B2) and copy down
Finally in D1 use =SUM(C:C) that should be the total you are looking for.

Change the item in C1 and its total will be in D1.

This should work if the quantity is anywhere in text and is max 999.

Of course the complete calculation could be in done all in the macro, but at the moment the specifications seem to me a little bit unstable.

Bye,

 

[acura123]

Thanks That worked great!