Formula help please!

[Peet]

Hi,

I have a formula that I have been using to calculate costs based on
reference letters, but now want to exclude some of the letters used in it so
it doesn't add these costs in to the equation.

My current formula is:

=LOOKUP('Exec
Sum'!$F$11,{0,"A/B","C","D","E","F","G","H","I","J","K","L","M","N","O"},'Market Summary'!$P$9:$P$21)+LOOKUP('Exec Sum'!$.....................

I want to exclude "C", "L", and "M" from the costs and have tried to put "0"
in their places but it still seems to count what was in their places anyway.
When I dont put anything in at all it also doesn't work.

Hope someone can help!
Thanks for looking

 

[Shane Devenshire]

Hi,

do something like this

=IF(OR(F11="C",F11="L"),0,LOOKUP($F$11,{0,"A/B","C","D","E","F","G","H","I","J","K","L","M","N","O"},$P$9:$P$21))

 

[Peet]

Hi Shane, I have put your formula in but it returns a result of zero when I
try to include more than one in the sum. Any ideas why this is happening?

=IF(OR('Exec Sum'!D11="C",'Exec Sum'!D11="L",'Exec
Sum'!D11="M"),0,LOOKUP($F$11,{0,"A/B","C","D","E","F","G","H","I","J","K","L","M"},'Market
Summary'!Q9:Q21)+IF(OR('Exec Sum'!D12="C",'Exec Sum'!D12="L",'Exec
Sum'!D12="M"),0,LOOKUP($F$11,{0,"A/B","C","D","E","F","G","H","I","J","K","L","M"},'Market Summary'!$Q$9:$Q$21)+IF..................