Hello! I am having a hard time adding a third variable to Sumproduct formula. Column C counts the instances between A and B from the reference table. The third variable has to subtract all instances less than 00:45. There is a count if statement that provides the correct answer in column E. I have included the formulas I have tried, but they are wrong or wildly off. Please help! The column the third variable is called PowerQ[Ticket Delivery Time].
| SumProduct(Subtotal(Offsett) - Formula Logic | |||||||||||||
| countIF(PowerQ[Ticket Delivery Time],"<00:45:00") | |||||||||||||
| 0:45 | COUNTIFS(PowerQ[Delivery Time2], ">=" & A5, PowerQ[Delivery Time2], "<" & B5, PowerQ[Ticket Delivery Time], ">0:45") | ||||||||||||
| Delivery Times | Ticket Printed | Trays Delivered | Late Trays with count if | Late Volume % | Late tray with Sumproduct | Time to Deliver | Total Delivered | ||||||
| 6:00:00 AM | 6:59:59 AM | 8 | 0 | 0% | 0:00 | 0:10 | 187 | 6886 | |||||
| 7:00:00 AM | 7:59:59 AM | 2107 | 930 | 203 | 22% | 0:11 | 0:20 | 1109 | SUMPRODUCT(SUBTOTAL(3,OFFSET(PowerQ[Print Time],ROW(powerq[Print Time])-ROW(powerq[Print Time]),0,1)),--(PowerQ[Print Time]<B6))-SUMPRODUCT(SUBTOTAL(3,OFFSET(PowerQ[Print Time],ROW(PowerQ[Print Time])-MIN(ROW(PowerQ[Print Time])),--(PowerQ[Print Time]<A6))-SUMPRODUCT(SUBTOTAL(3,OFFSET(PowerQ[Ticket Delivery Time],ROW(PowerQ[Ticket Delivery Time]),--(SUMPRODUCT(SUBTOTAL(3,OFFSET(PowerQ[Ticket Delivery Time],ROW(PowerQ[Ticket Delivery Time])-MIN(ROW(PowerQ[Ticket Delivery Time])),,1))*(PowerQ[Ticket Delivery Time],>"12:45:00 AM") | ||||
| 8:00:00 AM | 8:59:59 AM | 1108 | 1880 | 899 | 48% | 0:21 | 0:30 | 1972 | |||||
| 9:00:00 AM | 9:59:59 AM | 502 | 1064 | 527 | 50% | 0:31 | 0:44 | 3618 | -16758 | ||||
| 10:00:00 AM | 10:59:59 AM | 227 | 295 | 104 | 35% | 0:45 | 0:50 | 1315 | 5280 | ||||
| 11:00:00 AM | 11:59:59 AM | 2049 | 617 | 87 | 14% | 0:51 | 1:00 | 1395 | 2049 | ||||
| 12:00:00 PM | 12:59:59 PM | 1168 | 1872 | 915 | 49% | 1:01 | 1:10 | 1097 | 1168 | ||||
| 1:00:00 PM | 1:59:59 PM | 635 | 1223 | 559 | 46% | 1:11 | 1:20 | 525 | 635 | ||||
| 2:00:00 PM | 2:59:59 PM | 217 | 410 | 189 | 46% | 1:21 | 1:30 | 402 | SUMPRODUCT(SUBTOTAL(3,OFFSET(PowerQ[Print Time],ROW(PowerQ[Print Time])-MIN(ROW(PowerQ[Print Time]),0,1),--(PowerQ[Print Time]<B13),--(PowerQ[Print Time]<A13)),--(PowerQ[Ticket Delivery Time]>A3))) | ||||
| 3:00:00 PM | 3:59:59 PM | 200 | 153 | 63 | 41% | 1:31 | 11:59 | 587 | 200 | ||||
| 4:00:00 PM | 4:59:59 PM | 1848 | 685 | 80 | 12% | 1848 | |||||||
| 5:00:00 PM | 5:59:59 PM | 1248 | 1619 | 744 | 46% | 1248 | |||||||
| 6:00:00 PM | 6:59:59 PM | 456 | 1252 | 516 | 41% | 456 | |||||||
| 7:00:00 PM | 7:59:59 PM | 2 | 198 | 167 | 84% | 2 | |||||||
| Totals | 11767 | 12206 | 5053 | 38% | |||||||||
