Help with formula please

[Eamon]

Hi,

In Column A i have number

In Column B to H the cells maybe blank or contain any of the symbols ?, *, X

In Column I, i want to return the value of Column A less -5 for every
occurrence of the *, -10 for every occurrence of ? and -20 for every
occurrence of X, if the cell is blank return the value in column A

in columns
A B C D E F G H I
100 ? *
120 X ? X *
99
59 ? X ? ?
116 * *
the row numbers being used are 10:40

Hope i have explained this clearly, any suggestions will be most welcome.

Regards,

Eamon

 

[Guest]

Hi

Try this:
=A10-(COUNTIF(B10:H10,"~*")*5)-(COUNTIF(B10:H10,"~?")*10)-(COUNTIF(B10:H10,"
x")*20)
You can then fill this down the remaining rows.

 

[JulieD]

Hi Eamon

=A10-(COUNTIF(B10:H10,"*")*5)-(COUNTIF(B10:H10,"?")*10)-(COUNTIF(B10:H10,"X"
)*20)
copy down as necessary

Cheers
JulieD

 

[Frank Kabel]

Hi Julie
you have to add the tidle
=A10-(COUNTIF(B10:H10,"~*")*5)-(COUNTIF(B10:H10,"~?")*10)-(COUNTIF(B10:
H10,"X"
)*20)

 

[JulieD]

Hi Frank

why, it seemed to work when i tested it?

Cheers
JulieD

 

[JulieD]

Hi Frank

sorry, take that back ... when i tested it again it didn't work until i put
the tilde in ... must have just fluked it on the ones i tested before.

Cheers
JulieD

 

[Eamon]

Hi,

Thank you all so very much, excellent

Andy your formula works perfect

Julie i am getting -15 for *, -15 for ?, -35 for x

Frank your suggestion to Julie is correct

Again thank you Andy, Julie and Frank your efforts are much appreciated.

Best regards,

Eamon

 

[Guest]

Thanks for the feedback - glad you're sorted!!