SUMIF unmatched data

[stevotsky]

Hi,

Please help...

I would like to create sum data for vendors that are not alike...

For example,

Column1 Column2 Amount
wateryoux water $12
youaregreat great $1
greattnneess tinab $2
loowaterloo antique $3

I would like to do sumif(column1,**column2*",amount)

I have 10,000 data elements in column1, 500 in column2. All the data in column1 can be summarized in column2, just that they are not exact. So I am thinking I could say, if my formula looks in column1 and finds something that looks like column2, it should sum it. Makes sense?

Thanks for your help.

 

[Domenic]

Somewhat unclear... In your example, what's the answer that you're looking for?

 

[Robert Mika]

Try this
Data in A1:C4:
Array formula(confirm CTRL+SHIF+ENTER)
in D1 copied down:
=SUMPRODUCT(--(ISNUMBER(SEARCH(B1,$A$1:$A$4))),--($C$1:$C$4))

 

[stevotsky]

Try this
Data in A1:C4:
Array formula(confirm CTRL+SHIF+ENTER)
in D1 copied down:
=SUMPRODUCT(--(ISNUMBER(SEARCH(B1,$A$1:$A$4))),--($C$1:$C$4))

column1
alaska water ways
timbuktu highway
timbuktu roads
alaska-michigain
intercontinental airport inc
roads only inc

column 2
alaska water
timbuktu highways inc


column 3
200
300
400
150
63
71

For example, I would like a formula so that if, for example, column 2 contains alaska, then the formula will add 200 & 400 (the corresponding figures for items containing alaska per column 1)

I hope that is clearer.

Many thanks,

Steve

 

[Robert Mika]

Hi Steve,
I hope you meant "200+150",
This will take first word in string in cell B1 and match with column A
(confirm CTRL+SHIFT+ENTER)
=SUMPRODUCT(--(ISNUMBER(FIND(LEFT(B1,FIND(" ",B1)-1),$A$1:$A$6))),--($C$1:$C$6))

 

[Aladin Akyurek]

A bit faster...

=SUMIF($A$1:$A$6,LEFT(B1,FIND(" ",B1)-1)&"*",$B$1:$B$6)

if the first word commonality holds for all records.

 

[stevotsky]

Thanks all for your help with this!