Formula help

[rosaria93]

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?

[#2]

=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.

[#3 · Accepted solution]

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

[#4]

@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.

[#5]

@NikolinoDE 

I guess

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

[#6]

@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"))

[#7]

@NikolinoDE, 

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.  

[#8]

@daytamod 

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

[#9]

Thank you @Peter Bartholomew 

This is working perfectly

[#10]

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

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

[#11]

@Sergei Baklan 

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

[#12]

@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 @NikolinoDE like myself were responding in a way the solution would meet @rosaria93's way of approaching it.  I think both @Peter Bartholomew  and @NikolinoDE 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.