Discontinuous ranges
Posted by Larry Bruce on July 14, 1999 7:54 AM
I am "fine tuning" a retail store employee work-schedule sheet. At the bottom of each "Day" column I want to list two totals: "Opening Shifts" and "Closing Shifts." These totals are currently derived by using two hidden columns (adjacent to right): one column contains 1's for times earlier than 11:00am, else "0"; the adjacent hidden column gives similar figures for times >= 11:00am.
This "hidden column" method works, but is cumbersome and awkward.
I want to use the COUNTIF function, but my "shift start" times are not in a homogenous, continuous range.
When I try to "NAME" a discontinuous range consisting of some dozen or so "start time" cells, the resulting COUNTIF formula returns #VALUE! in both the range portion AND the resulting target cell. I hold down the CTRL key while choosing the discontinuous cells during the define process, but to no useful end.
Where must my thinking wander to overcome this dead-end frustration? It's gotta be simpler than this!