SUMIF unmatched data

stevotsky

New Member
Joined
Oct 15, 2006
Messages
11
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.
 

Some videos you may like

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
Joined
Mar 10, 2004
Messages
19,403
Somewhat unclear... In your example, what's the answer that you're looking for?
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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
Joined
Oct 15, 2006
Messages
11
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

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,994
Messages
5,575,423
Members
412,661
Latest member
joelgibney1
Top