If column a contains "xyz" then sum number from column b

[messin31]

Lets say I want the total sum value of all items containing "football" or "soccer":

<table border="0" cellpadding="0" cellspacing="0" width="163"><col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> <col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:74pt" height="20" width="99">A</td> <td style="width:48pt" width="64">B</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">football</td> <td class="xl65" align="right">$5 </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">football player</td> <td class="xl65" align="right">$15 </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">football jersey</td> <td class="xl65" align="right">$23 </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">soccer</td> <td class="xl65" align="right">$12 </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">soccer ball</td> <td class="xl65" align="right">$15 </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">tennis</td> <td class="xl65" align="right">$5 </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">tennis player</td> <td class="xl65" align="right">$5 </td> </tr> </tbody></table>

 

[RoryA]

If you mean each one then:
=SUMIF(A:A,"*football*",B:B)

 

[T. Valko]

Lets say I want the total sum value of all items containing "football" or "soccer":

<TABLE cellSpacing=0 cellPadding=0 width=163 border=0><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 74pt; HEIGHT: 15pt" width=99 height=20>A</TD><TD style="WIDTH: 48pt" width=64>B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>football</TD><TD class=xl65 align=right>$5 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>football player</TD><TD class=xl65 align=right>$15 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>football jersey</TD><TD class=xl65 align=right>$23 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>soccer</TD><TD class=xl65 align=right>$12 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>soccer ball</TD><TD class=xl65 align=right>$15 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>tennis</TD><TD class=xl65 align=right>$5 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>tennis player</TD><TD class=xl65 align=right>$5 </TD></TR></TBODY></TABLE>

Try this...

=SUMIF(A2:A10,"Football*",B2:B10)

Or, using a cell to hold the criteria:

D2 = Football

=SUMIF(A2:A10,D2&"*",B2:B10)

 

[Joe4]

Welcome to the Board!

If you want to include any record that contains "football" or "soccer", this should work:

=SUMIF(A2:A8,"*football*",B2:B8) + SUMIF(A2:A8,"*soccer*",B2:B8)

 

[Marcelo Branco]

To sum football or soccer maybe

=SUM(SUMIF($A$1:$A$7,{"*football*";"*soccer*"},$B$1:$B$7))

M.

 

[Biz]

To sum football or soccer maybe

=SUM(SUMIF($A$1:$A$7,{"*football*";"*soccer*"},$B$1:$B$7))

M.

Mate nice formula. I tried sumproduct and it aslow works too.

=SUMPRODUCT(SUMIF($A$1:$A$7,{"*football*";"*soccer*"},$B$1:$B$7))

Biz

 

[Proteus]

If you mean each one then:
=SUMIF(A:A,"*football*",B:B)

Rorya - such an elegant solution!
I'm a long time voyeur of this forum but upon seeing this you have inspired me to sign up. Not sure I'll be able to contibute much, but nice to be aboard. Cheers.

 

[RoryA]

Welcome aboard!

 

[jlmoss]

Sorry if you guys don't like old threads being bought up.

Just wanted to give my thanks for your guys help. The information in this thread helped me solve my problem of...

=SUMIF('Raw Data'!A:A,"Gas",'Raw Data'!D:D)

Again, much appreciated. Helps me straighten out my monthly expenditures a little easier. hehe