SUMIFS Syntax issue

Question

Good afternoon. I am attempting to write a SUMIFS formula and am having some issues with the syntax.

This is a copy of the code (currently functioning):

=SUMIFS(Table110[M],Table110[Date],$B3,Table110[Delay Type],C$2,Table110[Department],$C$1,Table110[Machine ID],"CL1")

I am looking to add the following code into the SUMIFS as two additional sets of criteria range and criteria. This code is intended to function as a two sided match to filter the data into two sets, starting at 5AM and 5PM.

Table110[Start Time],IF($A3="Day",">"&TIMEVALUE(05:00),"<"&TIMEVALUE(05:00)),Table110[Start Time],IF($A3="Day","<"&TIMEVALUE(17:00),">"&TIMEVALUE(17:00))''

I believe that the issue has something to do with the following syntax: ">"&

Any assistance would be sincerely appreciated.

Answer 1

The argument of TIMEVALUE must be a string: TIMEVALUE("05:00"). So the formula could be

=SUMIFS(Table110[M], Table110[Date],$B3, Table110[Delay Type], C$2, Table110[Department], $C$1, Table110[Machine ID], "CL1", Table110[Start Time], IF($A3="Day", ">", "<")&TIMEVALUE("05:00"), Table110[Start Time], IF($A3="Day", "<", ">")&TIMEVALUE("17:00"))

BUT!!! This won't work if A3 is not equal to "Day": the conditions for Start Time evaluate to "<05:00" and ">17:00", but that will never be satisfied simultaneously.

You'll have to use SUMPRODUCT (or SUM) instead:

=SUMPRODUCT(Table110[M], (Table110[Date]=$B3)*(Table110[Delay Type]=C$2)*(Table110[Department]=$C$1)*(Table110[Machine ID]="CL1")*IF($A3="Day", (Table110[Start Time]>TIMEVALUE("05:00"))*(Table110[Start Time]>TIMEVALUE("17:00")), (Table110[Start Time]<TIMEVALUE("05:00"))+(Table110[Start Time]>TIMEVALUE("17:00"))))

Finally: a start time of exactly 05:00 or 17:00 will never be included. You may want to use <= or >= for one condition.