# SUMIF unmatched data

#### stevotsky

##### New Member
Hi,

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?

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Domenic

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

#### Robert Mika

##### MrExcel MVP
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

##### New Member
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
timbuktu highway
intercontinental airport inc

column 2
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

##### MrExcel MVP

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))

##### MrExcel MVP
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

##### New Member
Thanks all for your help with this!

Replies
9
Views
210
Replies
2
Views
38
Replies
9
Views
296
Replies
4
Views
586
Replies
0
Views
72