200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi guys.
I want to count total records based on coumn A
A1 : 09:00-14:00
A2 : 1500:16:00
A3 : no input
A4: 10:00-11:00
A5:14:00-18:00
B1 : check coumn A end time is less or =16:00 will count 1 , this example will show 3 in b1.
I try use this formula but not success.
=SUMPRODUCT(--(TIMEVALUE(MID(A1:A6, FIND("-", A1:A6) + 1, LEN(A1:A6) - FIND("-", A1:A6))) <= TIME(16, 0, 0)))
thanks in advanced.
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.
Hi John and thanks to help again.
=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)) or =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)) in the formula bar confirm it with Ctrl+Shift+Enter will work perfect.