Selected Answer

Hi Fai and welcome to the Forum.

In the attached file I've created two working *array* formulas...

In cell B1 (yellow) you will see:

`=SUMPRODUCT(IFERROR(IF(TIMEVALUE(RIGHT(TRIM(`**A:A**),5))<=TIME(16,0,0),1,0),0), IFERROR(IF(FIND("-",**A:A**)>0,1,0),0))

which works on all of column A (as per your question working) and detects "-" in the cells and if the last 5 characters indicate a time <16:00.

B6 (green) is similar but works on just A1:A5-

`=SUMPRODUCT(IFERROR(IF(TIMEVALUE(RIGHT(TRIM(`**A1:A5**),5))<=TIME(16,0,0),1,0),0), IFERROR(IF(FIND("-",**A1:A5**)>0,1,0),0))

Hope this fixes your problem. If so, please be sure to mark this Answer as *Selected*.

**Revision #1, 22 September 2024**

Please note that this Answer relies on *array formulas*. In Excel 365, and 2021, they can be entered (or edited) and confirmed with the Enter key. In legacy versions (e.g. 2019 and earlier) you need to confirm the formula using the key combination *Ctrl+Shift+Enter* or they will produce the wrong result.