How to return value if any cells are blank

[megera716]

I have 3 columns of data with my formula in column 4. If any of the 3 columns are blank, I want the formula to return 0. If all 3 have data, then it should return 50.

Here's my formula, which I am doing as an array: {=IF(ISBLANK(Table1[@[1]:[3]]),"","50")}. But on the row in red text, it is returning 50 even though column 3 is blank (and yes, it is completely blank, not a formula returning a blank).

1	2	3	4
01/23/2018	01/26/2018	X	50
		X
		X
01/25/2018	01/26/2018	X	50
		X
01/24/2018	01/24/2018		50
01/15/2018	01/26/2018	X	50
01/25/2018	01/26/2018	X	50

<tbody>
</tbody>

 

[Aladin Akyurek]

Try in D2...

=(COUNTA(A2:C2)=3)*50

 

[megera716]

Thanks!! That worked, although I don't know how it worked without it being IF COUNTA = 3?

 

[megera716]

Actually, scratch that. it still returned 50 for one row where column 3 was blank (but curiously enough, it correctly did NOT return the 50 for the row that was returning 50 before) .

 

[Joe4]

it still returned 50 for one row where column 3 was blank

Are you sure it is really blank (and doesn't contain something like a single space)?
You can easily confirm that with the LEN function.
If it is cell C2, then:
=LEN(C2)
should return 0. If it does not, then the cell is not really blank.

 

[megera716]

Well that's embarrassing. That fixed it Joe. There were indeed spaces in the cells - how annoying (it was a download from a website but I don't know why it would have a space in it!).

 

[Joe4]

it was a download from a website but I don't know why it would have a space in it!

It is actually quite common. There are often non-typical characters too, like non-breaking spaces on files download from the web.

 

[Joe4]

I don't know how it worked without it being IF COUNTA = 3?

To answer this question, it is taking advantage of the fact that it is a Boolean expression.
If you set two things equal to each other, than is either a TRUE statement or a FALSE statement.
TRUE has a numerical equivalent of 1, and FALSE has a numerical equivalent of 0.

To see this, first try these two expressions:
=1=1
=1=0
This is pretty obvious, and you will get TRUE and FALSE respectively, for your answers.

Now add this to each one:
=(1=1)*50
=(1=0)*50
and note the answers (which shows how TRUE=1 and FALSE=0).