Sum based on containing text

[adeelmush]

Hi,
I am looking for a function which i can use to sum up the visits, based on the containing text. example shown below

Keywords Visits
accounts 1000
financial accounts 3030
audit accounts 1430
accounts manager 1203
audit 3420
finance 1000


So i am looking for a function which will allow me to sum up all the visits which contains 'accounts' in keywords cell.

I know a function which counts the cell based on the contain criteria but i am unable to add up the visits based on the contain criteria.

i looking forward to hear for ur suggestions

Regards
Adeel

 

[crook_101]

Hi,

Something like this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Keywords</td><td style=";">Visits</td><td style=";">Sum</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">accounts </td><td style="text-align: right;;">1000</td><td style="text-align: right;;">6663</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">financial accounts</td><td style="text-align: right;;">3030</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">audit accounts</td><td style="text-align: right;;">1430</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">accounts manager</td><td style="text-align: right;;">1203</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">audit </td><td style="text-align: right;;">3420</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">finance </td><td style="text-align: right;;">1000</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=SUMIF(<font color="Blue">A2:A6,"*accounts*",B2:B6</font>)</td></tr></tbody></table></td></tr></table><br />

Expand range to cover your data range.

Regards,
Ian

 

[adeelmush]

Hi crook_101,
thanks a lot for the help, it really helps me for my reporting. just a quick question is it possible to do it on the basis of cell instead of specifying text within the sumif function.

many thanks

adeel

 

[pgc01]

Hi Adeel

With the lookup value in D2, try:

=SUMIF(A2:A6,"*"&D2&"*",B2:B6)