• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct formulas to exclude specific text/values in count and totals


New Member
The file I need help with is attached.

What I want to change in this file is to include “IN” and “OFF” on the calendar days that employees are working and have off days but I do not want these words to be counted in the "Leaves This Month", "Leaves This Year" and "Leave Breakup" as leave days. So I want to use it as an work attendance tracker too. All 10 employees have different start days and off days. Which formulas do I need to change please to achieve the aforementioned?

Thank you very much in advance.



Excel Ninja
Staff member
First type these values in NY8:NY17
1, 1, 1, 1, 0, 0, 1, 1, 0.5, 0.5

Then in NJ8:

Copy down

I'm sure you'll work out the YEAR Column

or see the attached file:

ps: Cross Posting to multiple sites is not good practice and not recommended
Please read and comply with the site rules