SOLVED

Formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-1577976%22%20slang%3D%22en-US%22%3EFormula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1577976%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20smart%20excel%20people%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20with%20a%20formula.%20I%20want%20to%20return%20the%20number%201%20only%20if%20cell%201%20is%20blank%20AND%20cell%202%20equals%20and%20exact%20text%2C%20otherwise%20return%200%20or%20blank.%20I%20can%20make%20each%20separate%20part%20work%20independently%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(B45%20%3D%20%22%22%2C%201)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(O45%3D%22Music%20Master%20Online%22%2C%201)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20when%20I%20try%20and%20join%20them%20together%2C%20the%20closest%20I've%20got%20to%20is%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E%3DIF(B45%3D%22%22%2CAND(O45%3D%22Music%20Master%20Online%22)%2C%221%22)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EThis%20formula%20returns%20TRUE%20or%20FALSE%20-%20How%20do%20I%20make%20it%20return%20the%20number%201%20or%200%20instead%3F%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1577976%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578006%22%20slang%3D%22de-DE%22%3ESubject%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578006%22%20slang%3D%22de-DE%22%3E%3DIF(B45%3D%22%22%2C%221%22%2CIF(O45%3D%22Musikmaster%20Online%22%2C%221%22%2C%220%22))%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%20%3CBR%20%2F%3E%20*%20Beware%20of%20scammers%20posting%20fake%20support%20numbers%20here.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578029%22%20slang%3D%22en-US%22%3EBetreff%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578029%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E-%20thank%20you%20so%20much%20-%20I've%20been%20at%20that%20for%203%20hours%20-%20Thank%20you%20thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578033%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578033%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F754603%22%20target%3D%22_blank%22%3E%40rosaria93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20both%20conditions%20are%20satisfied%20the%20formula%20returns%20the%20number%201%2C%20else%200.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20SIGN(%20AND(%20cell1%26lt%3B%26gt%3B%22%22%2C%20cell2%3D%22Music%20Master%20Online%22%20)%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EWithout%20the%20SIGN%20it%20would%20return%20TRUE%20or%20FALSE.%26nbsp%3B%20The%20cells%20refer%20to%20your%20directly%20referenced%20cells%2C%20except%20that%20I%20moved%20them.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578038%22%20slang%3D%22en-US%22%3EBetreff%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578038%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D(B45%3D%22%22)*(O45%3D%22Musikmaster%20Online%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578050%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578050%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F754603%22%20target%3D%22_blank%22%3E%40rosaria93%3C%2FA%3E%26nbsp%3BYou%20have%20a%20choice%2C%20they%20both%20do%20the%20same%20thing.%26nbsp%3B%20Seeing%20as%20AND%20without%20will%20return%20the%20TRUE%20%2F%20FALSE%20(which%20can%20still%20work%20for%20you)%20the%20SIGN%20function%20return%20it%20as%201%20%2F%200%20respectively.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(B45%3D%22%22%2CO45%3D%22Music%20Master%20Online%22)%2C1%2C0)%3C%2FP%3E%3CP%3Eor%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSIGN(AND(B45%3D%22%22%2CO45%3D%22Music%20Master%20Online%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578054%22%20slang%3D%22en-US%22%3EBetreff%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578054%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%2C%26nbsp%3B%3C%2FP%3E%3CP%3Eunfortunately%20the%20formula%20is%20still%20not%20100%25%20correct.%26nbsp%3B%3C%2FP%3E%3CP%3Eeg%20If%20my%20first%20cell%20is%20blank%26nbsp%3B%20and%26nbsp%3B%20my%20second%20cell%20has%20%22different%20text%22%20%2C%20it%20returns%201.%20It%20should%20return%200.%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578061%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578061%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368919%22%20target%3D%22_blank%22%3E%40daytamod%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20correct%2C%20but%20why%20do%20we%20need%20all%20these%20extra%20IF%2C%20AND%2C%20SIGN%20in%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578069%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578069%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20working%20perfectly%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578081%22%20slang%3D%22de-DE%22%3ESubject%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578081%22%20slang%3D%22de-DE%22%3EYou%20welcome%20%3CBR%20%2F%3E%20I%20was%20pleased%20that%20it%20led%20to%20a%20solution%20for%20you.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578088%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578088%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20it%20to%20avoid%20having%20to%20explain%20why%20'%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E*%3C%2FSTRONG%3E%3C%2FFONT%3E'%20and%20'%3CFONT%20color%3D%22%23FF0000%22%3EAND%3C%2FFONT%3E'%20are%20equivalent.%26nbsp%3B%20Sheer%20cowardice!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1591563%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1591563%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23800080%22%3E%22That's%20correct%2C%20but%20why%20do%20we%20need%20all%20these%20extra%20IF%2C%20AND%2C%20SIGN%20in%20formula%3F%22%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3EWho%20said%20they%20are%20needed%20%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBecause%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F754603%22%20target%3D%22_blank%22%3E%40rosaria93%3C%2FA%3E%26nbsp%3Bwas%20already%20applying%20the%20logic%20of%20%3CSTRONG%3EIF%3C%2FSTRONG%3E%2C%20I%20believe%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%20and%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3Blike%20myself%20were%20responding%20in%20a%20way%20the%20solution%20would%20meet%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F754603%22%20target%3D%22_blank%22%3E%40rosaria93%3C%2FA%3E's%20way%20of%20approaching%20it.%26nbsp%3B%20I%20think%20both%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%20and%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3Bprovided%20a%20good%20approach%20to%20answer%20the%20question.%26nbsp%3B%20Which%20one%20works%20best%20and%20makes%20the%20most%20sense%20is%20down%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F754603%22%20target%3D%22_blank%22%3E%40rosaria93%3C%2FA%3E%2C%20not%20to%20any%20of%20us.%26nbsp%3B%20As%20long%20as%20we%20are%20all%20providing%20positive%20support%20and%20the%20answer%20is%20correct%20I%20will%20not%20judge%20another%20person's%20approach%20to%20the%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi smart excel people, 

I need help with a formula. I want to return the number 1 only if cell 1 is blank AND cell 2 equals and exact text, otherwise return 0 or blank. I can make each separate part work independently 

 

=IF(B45 = "", 1)

 

=IF(O45="Music Master Online", 1)

 

But when I try and join them together, the closest I've got to is 

=IF(B45="",AND(O45="Music Master Online"),"1")

This formula returns TRUE or FALSE - How do I make it return the number 1 or 0 instead?

 

 

11 Replies
Highlighted
=IF(B45="","1",IF(O45="Musikmaster Online","1","0"))

Nikolino
I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
* Beware of scammers posting fake support numbers here.
Highlighted
Best Response confirmed by rosaria93 (New Contributor)
Solution

@Nikolino- thank you so much - I've been at that for 3 hours - Thank you thank you!

 

Highlighted

@rosaria93 

If both conditions are satisfied the formula returns the number 1, else 0.

= SIGN( AND( cell1<>"", cell2="Music Master Online" ) )

Without the SIGN it would return TRUE or FALSE.  The cells refer to your directly referenced cells, except that I moved them.

Highlighted

@Nikolino 

I guess

=(B45="")*(O45="Musikmaster Online")

@rosaria93 You have a choice, they both do the same thing.  Seeing as AND without will return the TRUE / FALSE (which can still work for you) the SIGN function return it as 1 / 0 respectively. 

 

=IF(AND(B45="",O45="Music Master Online"),1,0)

or 

=SIGN(AND(B45="",O45="Music Master Online"))

Highlighted

@Nikolino

unfortunately the formula is still not 100% correct. 

eg If my first cell is blank  and  my second cell has "different text" , it returns 1. It should return 0.  

Highlighted

@daytamod 

That's correct, but why do we need all these extra IF, AND, SIGN in formula?

Highlighted

Thank you @Peter Bartholomew 

This is working perfectly

Highlighted
You welcome
I was pleased that it led to a solution for you.

Nikolino
I know I don't know anything (Socrates)
Highlighted

@Sergei Baklan 

I did it to avoid having to explain why '*' and 'AND' are equivalent.  Sheer cowardice!

Highlighted

@Sergei Baklan

"That's correct, but why do we need all these extra IF, AND, SIGN in formula?"

Who said they are needed ? 

 

Because @rosaria93 was already applying the logic of IF, I believe @Peter Bartholomew  and @Nikolino like myself were responding in a way the solution would meet @rosaria93's way of approaching it.  I think both @Peter Bartholomew  and @Nikolino provided a good approach to answer the question.  Which one works best and makes the most sense is down to @rosaria93, not to any of us.  As long as we are all providing positive support and the answer is correct I will not judge another person's approach to the solution.