If Countif and Substitute combined - adding another value into if function

[kingofcamden]

Hi everyone,

I'm currently working on a spreadsheet that copies content from one worksheet to another whilst replacing dynamic strings with specific content based on a locale.

The formula that is working so far is:

Please Login or Register  to view this content.

However, now I have a 3rd criteria "*Asia*" to include into the if function for which the "[Rew]" should be replaced with "LoyaltyAsia" when copying over. I tried to change this to be an IFS formula, but just can't get it to work :-(

Hope someone can help?
Many thanks!

[6StringJazzer]

IFS is available only if you have Excel 2016 and a subscription to Office 365. Your profile shows you are using Excel 2010, so you can't use IFS.

Please Login or Register  to view this content.

[63falcondude]

It's difficult to work without a small representative sample of your data along with the desired results but I'll give it a try based on what you have shared.

Try this:

=SUBSTITUTE(LOCFile!C2,"[Rew]",IF(SUM(COUNTIF($A2,{"*LATA*","*_es*","*Eses*","*LATAM*"})),"Loyalty",IF(COUNTIF($A2,"*Asia*"),"LoyaltyAsia","Loyalty123")))

or a little shorter:

="Loyalty"&SUBSTITUTE(LOCFile!C2,"[Rew]",IF(SUM(COUNTIF($A2,{"*LATA*","*_es*","*Eses*","*LATAM*"})),"",IF(COUNTIF($A2,"*Asia*"),"Asia","123")))

[kingofcamden]

Hi 6StringJazzer & 63falcondude,

Thanks so much for your comments and help! I'm working with Excel 2016 (sorry, should've updated that in my profile!), however, the formula 63falcondude suggested works great!:

Please Login or Register  to view this content.

Only thing is that I realised there is another IF clause I need to build in (annoyingly!). I tried the following, but it doesn't seem to work ("too many arguments for this function") - any chance you could have a look please?

Please Login or Register  to view this content.

Appreciate your help!

[kingofcamden]

Ahh, false alarm! Managed to get it to work. Forgot to include {}

Thanks for your help!

[63falcondude]

You're welcome. Happy to help.