View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
joeu2004[_2_] joeu2004[_2_] is offline
external usenet poster
 
Posts: 829
Default 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.