find a word in sentences then use it's number

pmncivil said:

hi i need to identify a word like car in a bunch of sentences in a column then sum their numbers into a cell.
i explain it more:

...a sentence which contain car ........	2
...........book..........	4
..................car...............	32
.............car	12

<tbody>
</tbody>

i need to sum those numbers which contain car in their sentences.
like:
sum of cars = 2+32+12
books = 4 + ......
i can use this formula now : e1=is car
sumif(a:a,e1,b:b) but i dont know how to identify car in a sentence then use it's number in new cell.
by the way i'm new in excel and i don't know anything about programming.
so plz say in detail if there is any solution.
thank u

Click to expand...

If I understand what you are attempting to do, then I think this formula should work for you...

=SUMIF(A:A,E1,B:B)

Note that I used whole column references because that is what you showed in your fomula, however, using a smalle defined range for each would be more efficient.

i dont have any programming addones in excel so if i should use it plz send my a free link to downl.ad it

pmncivil said:

i dont have any programming addones in excel so if i should use it plz send my a free link to downl.ad it

Click to expand...

What I posted is a formula to be entered in a cell.

thank u sir it was my first formula which doesn't identify:: if there is car in a sentence or not
for example i have these sentences a column:

car to michel = 5
chair = 1
car for sara = 6
book=5
car in hospital = 2
mom's car = 1

here the car is the key word
i need excel to identify there is car in some sentences then sum their numbers: 5+6+2+1
sum of car = 5+6+2+1

Are the sentences and number in the same cell?

I think you need:

=SUMIF(A:A,"*"&E1&"*",B:B)

No sir they are not in a same cell
A1 is sentence b1 is the value

FormR said:

I think you need:

=SUMIF(A:A,"*"&E1&"*",B:B)

Click to expand...

Can u explain it to me? Plz?
I didn't get what does that mean
"*"&e1&?????

The * is a wildcard character it indicates that there can be any text before and any text after because it was used before and after E1

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:133px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >car to michel </td><td style="text-align:right; ">5</td><td > </td><td style="text-align:right; ">14</td><td >car</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >chair </td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >car for sara </td><td style="text-align:right; ">6</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >book</td><td style="text-align:right; ">5</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >car in hospital </td><td style="text-align:right; ">2</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >mom's car </td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D1</td><td >=SUMIF(A:A,"*"&E1&"*",B:B)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>