Array formula that returns single value for multiple criteria

[Henk Stander]

I need an array formula that returns the following (see data below):

“Missing Data!!” =IF(COUNTIF(E3:E33,"TRUE">0),"Missing Data!!","")

Regards,
Henk

DATA (File attached):

A B C E
Day of the month "D/S“ “N/S”
01-Jan-15-Thu 0 1 FALSE =AND(TODAY()>=A3,OR(B3="",C3=""))
02-Jan-15-Fri 0 1 FALSE =AND(TODAY()>=A4,OR(B4="",C4=""))
03-Jan-15-Sat 1 0 FALSE =AND(TODAY()>=A5,OR(B5="",C5=""))
04-Jan-15-Sun 1 0 FALSE =AND(TODAY()>=A6,OR(B6="",C6=""))
05-Jan-15-Mon 2 1 FALSE =AND(TODAY()>=A7,OR(B7="",C7=""))
06-Jan-15-Tue 1 2 FALSE =AND(TODAY()>=A8,OR(B8="",C8=""))
07-Jan-15-Wed 3 TRUE =AND(TODAY()>=A9,OR(B9="",C9=""))
08-Jan-15-Thu TRUE =AND(TODAY()>=A10,OR(B10="",C10=""))
09-Jan-15-Fri TRUE =AND(TODAY()>=A11,OR(B11="",C11=""))
10-Jan-15-Sat TRUE =AND(TODAY()>=A12,OR(B12="",C12=""))
11-Jan-15-Sun TRUE =AND(TODAY()>=A13,OR(B13="",C13=""))
12-Jan-15-Mon TRUE =AND(TODAY()>=A14,OR(B14="",C14=""))
13-Jan-15-Tue TRUE =AND(TODAY()>=A15,OR(B15="",C15=""))
14-Jan-15-Wed TRUE =AND(TODAY()>=A16,OR(B16="",C16=""))
15-Jan-15-Thu TRUE =AND(TODAY()>=A17,OR(B17="",C17=""))
16-Jan-15-Fri TRUE =AND(TODAY()>=A18,OR(B18="",C18=""))
17-Jan-15-Sat TRUE =AND(TODAY()>=A19,OR(B19="",C19=""))
18-Jan-15-Sun TRUE =AND(TODAY()>=A20,OR(B20="",C20=""))
19-Jan-15-Mon TRUE =AND(TODAY()>=A21,OR(B21="",C21=""))
20-Jan-15-Tue TRUE =AND(TODAY()>=A22,OR(B22="",C22=""))
21-Jan-15-Wed TRUE =AND(TODAY()>=A23,OR(B23="",C23=""))
22-Jan-15-Thu TRUE =AND(TODAY()>=A24,OR(B24="",C24=""))
23-Jan-15-Fri TRUE =AND(TODAY()>=A25,OR(B25="",C25=""))
24-Jan-15-Sat TRUE =AND(TODAY()>=A26,OR(B26="",C26=""))
25-Jan-15-Sun TRUE =AND(TODAY()>=A27,OR(B27="",C27=""))
26-Jan-15-Mon TRUE =AND(TODAY()>=A28,OR(B28="",C28=""))
27-Jan-15-Tue FALSE =AND(TODAY()>=A29,OR(B29="",C29=""))
28-Jan-15-Wed FALSE =AND(TODAY()>=A30,OR(B30="",C30=""))
29-Jan-15-Thu FALSE =AND(TODAY()>=A31,OR(B31="",C31=""))
30-Jan-15-Fri FALSE =AND(TODAY()>=A32,OR(B32="",C32=""))
31-Jan-15-Sat FALSE =AND(TODAY()>=A33,OR(B33="",C33=""))

20 =COUNTIF(E3:E33,"TRUE">0)

[daddylonglegs]

You probably just need the >0 outside the COUNTIF function, like this

=IF(COUNTIF(E3:E33,"TRUE")>0,"Missing Data!!","")

[Jonmo1]

I believe the syntax should be
=IF(COUNTIF(E3:E33,TRUE)>0,"Missing Data!!","")

[Henk Stander]

I believe the syntax should be
=IF(COUNTIF(E3:E33,TRUE)>0,"Missing Data!!","")

Thanks Jonmo1, daddylonglegs, have a look at the attached spreadsheet please. The formula you gave is what I had, i.e. if it is done "one be one", I need a single array formula to replace all that.

Regards

[daddylonglegs]

OK, I see, you want to get rid of the column E formulas? Try this formula

=IF(SUMPRODUCT((A6:A33<=TODAY())*(LEN(B6:B33&C6:C33)=0)),"Missing Data!!","")

[Jonmo1]

The formula you gave is what I had

Actually, No it's not.
There is a significant syntax discrepancy in what you 'Had' vs. What we posted.

You Had : =IF(COUNTIF(E3:E33,"TRUE">0),"Missing Data!!","")
We Have: =IF(COUNTIF(E3:E33,TRUE)>0,"Missing Data!!","")

Yours still provided the desired result, but it was merely coincidental.


Anyway, your post wasn't clear that you were looking to accomplish the overall task with 1 formula.
It seemed that you were saying the formula you tried wasn't working...


Try this
=IF(SUMPRODUCT(--(TODAY()>=A3:A33),--((B3:B33="")+(C3:C33="")>0))>0,"Missing Data!!","")

[Henk Stander]

@daddylonglegs:
Thank you sir, your formula works, but only if both columns B and C have data missing in the same row.
=IF(SUMPRODUCT((A6:A33<=TODAY())*(LEN(B6:B33&C6:C33)=0)),"Missing Data!!","")

@Jonmo1:
Thank you sir, your formula works perfectly.
=IF(SUMPRODUCT(--(TODAY()>=A3:A33),--((B3:B33="")+(C3:C33="")>0))>0,"Missing Data!!","")

I got your point on the syntax, thank you.
I am not sure why your formula does work, even though it is not an array formula. Clearly there is still a lot to be learned. I would appreciate it if you could explain.

Thank you very much,
Regards
Henk