Home

Excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-810181%22%20slang%3D%22en-US%22%3EExcel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810181%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I'm%20wanting%20to%20create%20a%20formula%20where%20I%20want%20something%20like%20this.%3C%2FP%3E%3CP%3Eif%20C2%2BE2%2BH2%20%3D%20B2%20and%20J2%3Da%20then%20n2%3Da%20or%20r%20if%20not%20true.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20gone%20through%20the%20tutorials%20but%20it's%20just%20doing%20my%20head%20in%20and%20I%20can't%20work%20it%20out.%3C%2FP%3E%3CP%3Eany%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWobly%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-810181%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-810202%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810202%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394311%22%20target%3D%22_blank%22%3E%40woblypegs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20below%20formula%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(AND(C2%2BE2%2BH2%3DB2%2CJ2%3DA2)%2CA2%2CR2)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESample%20file%20is%20also%20attached%20for%20your%20reference.%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-810208%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810208%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394311%22%20target%3D%22_blank%22%3E%40woblypegs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20per%20your%20description%2C%20please%20try%20this...%3C%2FP%3E%3CP%3EIn%20N2%3C%2FP%3E%3CP%3E%3DIF(AND(SUM(C2%2CE2%2CH2)%3DA2%2CJ2%3D%22a%22)%2C%22a%22%2C%22r%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20advantage%20of%20using%20SUM(C2%2CE2%2CH2)%20over%20C2%2BE2%2BH2%20is%2C%20if%20in%20any%20case%20C2%20or%20E2%20or%20H2%20contains%20a%20text%20string%20or%20a%20null%20string%20returned%20by%20another%20formula%2C%20C2%2BE2%2BH2%20will%20throw%20an%20error%20as%20you%20cannot%20add%20a%20string%20to%20a%20number%20whereas%20SUM(C2%2CE2%2CH2)%20will%20ignore%20the%20text%20values.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-810479%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810479%22%20slang%3D%22en-US%22%3EThank%20You%20very%20much.%3CBR%20%2F%3E%3CBR%20%2F%3EWobly%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-810480%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810480%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much.%3CBR%20%2F%3E%3CBR%20%2F%3EWobly%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-810623%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810623%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20Wobly!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESubodh%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
woblypegs
New Contributor

Hi I'm wanting to create a formula where I want something like this.

if C2+E2+H2 = B2 and J2=a then n2=a or r if not true.

 

I've gone through the tutorials but it's just doing my head in and I can't work it out.

any help would be greatly appreciated.

 

Many thanks

 

Wobly

5 Replies

hi @woblypegs 

 

You can use below formula

=IF(AND(C2+E2+H2=B2,J2=A2),A2,R2)

Sample file is also attached for your reference.

Tauqeer

@woblypegs 

 

As per your description, please try this...

In N2

=IF(AND(SUM(C2,E2,H2)=A2,J2="a"),"a","r")

 

The advantage of using SUM(C2,E2,H2) over C2+E2+H2 is, if in any case C2 or E2 or H2 contains a text string or a null string returned by another formula, C2+E2+H2 will throw an error as you cannot add a string to a number whereas SUM(C2,E2,H2) will ignore the text values.

Thank You very much.

Wobly
Thank you very much.

Wobly

You're welcome Wobly!

 

Subodh

Related Conversations