Sum of value based on if all keywords in text thread are present

[media_karen]

Hi all,

The titles says it all. I would like to get a sum value via lookup based on if the text thread matches my search criteria. Below is an example of what I would like to do!

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 355"]
<tbody>[TR]
[TD="class: xl65, width: 355"]Placement Name [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Spend [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 355"]
<tbody>[TR]
[TD="class: xl65, width: 355"]NAT_STA_DIS_MOB_BAN_STA_INAPP_300x250_DCM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"] $100.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 355"]
<tbody>[TR]
[TD="class: xl65, width: 355"]NAT_STA_DIS_MOB_BAN_STA_MW_300x250_DCM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"] $100.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 355"]
<tbody>[TR]
[TD="class: xl65, width: 355"]NAT_STA_DIS_MOB_BAN_STA_INAPP_728x90_DCM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"] $200.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 355"]
<tbody>[TR]
[TD="class: xl65, width: 355"]NAT_STA_DIS_MOB_BAN_STA__MW_728x90_DCM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"] $150.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Mobile
[/TD]
[TD]Spend[/TD]
[/TR]
[TR]
[TD]MOB INAPP[/TD]
[TD]$300.00[/TD]
[/TR]
[TR]
[TD]MOB MW[/TD]
[TD]$250.00[/TD]
[/TR]
</tbody>[/TABLE]

I would like my formula to search though column A on the first able and find all text strings with the words "MOB" & "INAPP" in them. Then it would pull the values corresponding to that string and sum it up. Currently, I have a formula that works with one keyword. But with more variations of the string, I need at least two keywords to make it work. Is that even possible?

Thanks in advance!

 

[cchavez24]

I don't know how to do that from scratch. If you can make it work for one text phrase, then use the and() formula and use the same technique for each desired criteria.

Hope this helps

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Placement Name​

[/td][td]

Spend​

[/td][td][/td][td]

Keywords​

[/td][td]

Spend​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

NAT_STA_DIS_MOB_BAN_STA_INAPP_300x250_DCM​

[/td][td]

100,00​

[/td][td][/td][td]

MOB INAPP​

[/td][td]

300​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

NAT_STA_DIS_MOB_BAN_STA_MW_300x250_DCM​

[/td][td]

100,00​

[/td][td][/td][td]

MOB MW​

[/td][td]

250​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

NAT_STA_DIS_MOB_BAN_STA_INAPP_728x90_DCM​

[/td][td]

200,00​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

NAT_STA_DIS_MOB_BAN_STA__MW_728x90_DCM​

[/td][td]

150,00​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in E2 copied down
=SUMIF($A$2:$A$5,"*"&SUBSTITUTE(D2," ","*")&"*",$B$2:$B$5)

Hope this helps

M.