@TheOcean
That may be the longest formula I've ever seen.
There is a reason (actually there are more than one reason) why many textbooks on Excel recommend strongly against writing formulas that are long. You're in the midst of learning some of them. Primary reason, though, is this: It's extremely difficult to diagnose the cause when a formula stops working (or never really works in the first place). This is especially true if somebody else wrote the formula.
- One Solution: use helper columns. Translation, break it down into sub-functions or formulas. Don't try to pack it all into one unintelligible formula.
- A more fundamental, factor: the overall design of your workbook is a major contributor to the problem. Without knowing the history of this, just looking at it leads me to the supposition that you've taken what used to be a paper-based tracking system and "automated it" by continuing to use the paper-based paradigm of record keeping (one sheet per month, one cell per day per employee in each monthly sheet, entering an "L" representing "leave" into those cells)....then asking Excel to count 'em.
- That's just NOT an effective way to accomplish the business need using Excel.
- You'd be much better served by a re-design that tracks attendance, vacation, leaves, whatever...as a single database.
- And then learn how to use something like the Pivot Table to produce your count.
- At this point, if I were meeting with you face-to-fact, I'd recommend against trying to fix this dysfunctional formula--which is akin to just finding a bigger bandage to cover a wound. Instead, work to achieve a deeper solution. Don't just relieve the symptoms; find the cure for the underlying condition.
Is such an approach--a radical redesign--thinkable? If so, you'll need to provide a deeper description of what the overall process is intended to achieve. Are you also tracking other aspects of employee history, for example? What is the bigger picture?
If you're not open to a redesign, then use "helper columns" and break the formula you have into sub-formulas. You clearly know how to write working formulas; you've just made the mistake of trying to pack all of the conditions into a single formula and are now experiencing the inevitable result--unintelligibility.
