Sum values based on a word in a text string

[Andy.C]

Hi All, really hope you can help!!

I need to sum values based on finding a word in a text string, but the word may be in a different place in each string. eg....

South York Distribution
Research East York
Unit 4 North York Goods inwards

The text will always be in the same column and the values in the adjacent column.

Thanks

Andy

 

[AlphaFrog]

This will sum the values in column B when "York" is found within an adjacent cell in column A

=SUMIF(A:A, "*York*", B:B)

Note: if you had a word like Yorkshire in column A, that would count as a match.

 

[VoG]

Try like this

Excel Workbook
	A	B	C	D
1	South York Distribution	1		6
2	Research East York	2
3	Unit 4 North York Goods inwards	3
4	East London	4
Sheet1

 

[Weaver]

Have a look at the =FIND() and =SEARCH() functions.

Are you looking to sum a value in another column depending on these results? If so, maybe countif with wildcards

=COUNTIF(A1:A4,"*york*")

 

[Andy.C]

Fantastic, thanks guys