Sumproduct not working
"joeu2004" wrote (with errata):
=COUNTIFS(B2:B6,"*4PM-6PM Start*",A2:A6,"<")
[....]
Caveat: The condition "<" might not work exactly A2:A6<"" above, IIRC.
Experiment to be sure that it counts all the instances you want, and it
does not count instances you do not want.
Alternatively (works in all Excel versions):
=SUMPRODUCT(ISNUMBER(SEARCH("4PM-6PM Start",B2:B6))*(A2:A6<""))
COUNTIF(A1:A1,"<") is 0 only for any empty cells; that is, no formula and
no constant value. It is one for cells whose value is the null string.
SUMPRODUCT(--(A1:A1<"")) is 0 for empty cells as well as for cells whose
value is the null string.
|