Adding a sumif function into a formula

[Holly92]

Hi,

I have written this formula, but it doesn't work! I am trying to add 1 to the result of the first formula (in brackets), if there is a '&' symbol in AZ10. I think what is confusing it is that I mean for it to look for a & within text, for example, 'Mr & Mrs Smith'. For this reason, I included a ~.

The value returned for this formula is the correct result for the formula in brackets, but it does not add 1 where there is a '&' symbol on AZ10.

=((AZ22/$X$3*100)/100)+SUM(IF(AZ10="*~&*",1,0))


If anyone would be able to help fix this formula, I would be most appreciative.

Many thanks,

Holly

 

[shaowu459]

=((az22/$x$3*100)/100)+isnumber(find("&",az10))

 

[Holly92]

Perfect! Thank you

 

[Holly92]

Would it be possible to alter this formula =((az22/$x$3*100)/100)+isnumber(find("&",az10)) so that instead of adding 1, it will multiply the result (of the formula in brackets at the start of the command) by 2?

Many thanks,

Holly

 

[shaowu459]

I'm sorry, for language reason, i don't quite under stand what you mean... sorry....
isnumber(find("&",az10)) will return true or false. is the formula returns TRUE, +TRUE equals to +1, if the formula returns FALSE, +FALSE equals to +0. That means in math calculation,TRUE=1, FALSE=0.

 

[Holly92]

Sorry I didn't say that very clearly. What I mean is, are you able to help make this formula work?
=((AZ22/$X$3*100)/100)+SUM(IF(AZ10="*~&*",X2,0))

I would like the result of this part of the formula:
=((AZ22/$X$3*100)/100)

to be multiplied by 2, if AZ10 has '&' somewhere in the text.

Many thanks,

Holly

 

[shaowu459]

please try:

[COLOR=#333333]=((az22/$x$3*100)/100)*if(isnumber(find("&",az10)),2,1) [/COLOR]