Addind values if text is contained in cells

[Ranu]

Hey guys,

So here's the deal :

In my first tab, I have some raw data that is presented as such :

Column A contains text
Column B contains numbers

So that gives us something like :

text1 8
text2 19
text3 7

etc.

Now here's what I want to do :
In the cell C1 I will enter a text value.
I'd like to have a formula that is gonna check the entire A column for cells that CONTAIN (not strickly equal) the text value I put in C1. Everytime the C1 value is present in a cell in the column A, the number in the column B is added to the sum.

For example, if I put "potato" in C1, and my A and B columns are as follow :

potato chip 8
potato 9
salad 8
food 3
potatos 1

The formula should give me the result 8+9+1 = 18

Any idea how to get there ?

Thanks,

[canapone]

Hi,

in C1 "POTATO", in A1:A1000 the potato strings.

The formula sums the numbers after the first space.

Please Login or Register  to view this content.

to be confirmed with control+shift+enter

Regards

[NBVC]

Perhaps:

=SUMIF($A$1:$A$10,"*"&C1&"*",$B$1:$B10)

adjust ranges to suit.

[Fotis1991]

Hi

Using a helper column, you can use simple formula, to get your result.

In D1(Or enywhere else), put the formula.

=IF(ISERROR(SEARCH($C$1;A1));0;1)

Then, usung this simple formula, you get your result.

=SUMIF(D1:D5;"=1";B1:B5)

Hope to helps you.