Logic formula direction - complex equation based off COUNTIFS

Occasional Contributor

Hello - I'm looking for some direction on how to build a logic formula in the attached file that will find the specific shifts that went over the scheduled time. The HR system at my company has a glitch that sums the total hours worked by the count of shifts worked when pulling a schedule report (so if you worked a total of 6 hours across 3 shifts, it would say you worked 18 hours for the day), and this is causing issues with reporting on what shifts were worked over schedule/when/where. I'm familiar with nesting logic formulas, but I've been stumped for the past two weeks on how exactly to build out what I need.


Column N already tells when the conditions are partially necessary for the logic formula, but the logic formula I need also has to take into account the specific day of the shifts in question (Rows 2-6 are the same worker but different days although Column N values are all TRUE). Column G is the sum by count issue... I am trying to use Column H and F to resolve the issues in G.


Basically, in the demo file attached, Row 2 is the first entry for John Smith - the first shift is 7 hours off his total 10.6 hours worked, so H2 returns a positive value because he worked the full 7 hours. The second shift, Row 3, returns a negative value because H2 is greater than F3, thereby indicating that it was that shift that caused John Smith to work beyond his scheduled hours. Similarly, Rows 4-6 follow this pattern. H4-6 are all positive, indicating that John Smith did not work over his scheduled time on any shift. The last row, Row 7 with Mary Johnson, did see her work over her shift with H7 yielding a negative number.


Column H is manually calculated in the attached spreadsheet with Columns K/L automatically calculating the values I need for reporting. What I'm looking for is direction for Column H... Based off the values in Columns B/C/J...


  • If B/C/J > 1 and it's the first instance of B/C/J and J(row)<>J(above), subtract F from G...
  • If B/C/J > 1 and it's not the first instance of B/C/J and J(row)=J(above), subtract H from F...
  • if B/C/J > 1 and it's the last instance of B/C/J and J(row)=J(above), subtract F from H
  • if B/C/J < 1 and G > F, subtract F from G


I can nest a logic formula for if B/C/J > 1 and J(row)=J(above)/etc... but I do not know how to build a formula that looks at the first/last/not-first-or-last instance of B/C/J > 1.


Any support or direction is appreciated! Believe me - this is easier than going to the HR system developer and having them deglitch the system.

0 Replies