if formula

[Likeexcel]

Hi:
Need help writing a formula.

Currently i have this formul =IF(ISNA(MATCH(AY11,$BA$6,0)),"",AK11)

I want to add more to this formula that shows if column AZ equals account 42000 then add column A, C, B, AND E, if it does not equal to account 42000 then use this formula =IF(ISNA(MATCH(AY11,$BA$6,0)),"",AK11)

thank you.

 

[FKoenig]

Try: =IF(AZ11=42000,SUM(A11:C11,E11),IF(ISNA(MATCH(AY11,$BA$6,0)),"",AK11))

 

[Scott Huish]

Why are you using MATCH for a single cell comparison?

=IF(AZ11=42000,SUM(A11:C11,E11),IF(AY11=$BA$6,AK11,""))

 

[Likeexcel]

Thank you both for your replies.

A little change to the formula, if AZ equals account 42000 or 43000. Not just equals to account 42000.

Could you rewrite the formula for me?

thank you again.

 

[Scott Huish]

=if(or(az11={42000,43000}),sum(a11:c11,e11),if(ay11=$ba$6,ak11,""))

 

[Likeexcel]

Thank you very much!

 

[Likeexcel]

Hi Hotpepper:
I'm getting a false error when the account does not equal. how do i get rid of the error?

thank you.

 

[Likeexcel]

work on one row but the second row got the #value error.

 

[Likeexcel]

ok, here is my new formula
IF(OR(AZ11={42000,43000}),SUM(F11:H11,K11,M11)+IF($BL$7=N11,O11,""))

it works when all of these columns has a number entered in.

However, there is "#value" error if there is no number in O11.

There is "false" error if the account is not 42000 or 43000.

can you rewrite it so i wouldnt get these two errors?

thank you in advance for your help.

 

[Scott Huish]

=if(or(az11={42000,43000}),sum(f11:h11,k11,m11)+(($bl$7=n11)*o11),"")