Using SUMIF on common words

[Guest]

I would like to use the SUMIF function to add up cells containing common
words, but not necessarily the same word, i.e.

Cell1 contains party123 value 100
Cell2 contains party 456 value 50
Cell3 contains poser 122 value 100
Cell4 conatins partypooper value 100

Therefore total cells containing "party" = cell 1+cell 2 +cell 4 = 250

Is it possible to do this?

 

[Dave Peterson]

Is your data laid out nicely in columns (like descriptions in A1:A10 and values
in B1:B10)?

If yes:

=SUMPRODUCT(--ISNUMBER(SEARCH("party",A1:A10,1)),B1:B10)

 

[Don Guillett]

Try this ARRAY formula which must be entered/edited using ctrl+shift+enter

=SUM(IF(ISERR(FIND("party",A3:A6)),"",VALUE(TRIM(RIGHT(A3:A6,LEN(A3:A6)-FIND
("value",A3:A6)-4)))))

 

[Guest]

use the DSUM function

if your data headings are
[Name] and [Value]

Criteria will be

Name Value
party

this works..

 

[Guest]

=sumif("party*",range:range,1)

 

[Dave Peterson]

Maybe this...

=SUMIF(A1:A10,"party*",B1:B10)
or
=SUMIF(A1:A10,"*party*",B1:B10)