• 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

    Hui...

  • 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

stingerz

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.
 

Attachments

  • EXCEL ISSUES HELP (1).xlsm
    110.5 KB · Views: 5
First type these values in NY8:NY17
1, 1, 1, 1, 0, 0, 1, 1, 0.5, 0.5

Then in NJ8:
=SUMPRODUCT(--(OFFSET($A8,0,31*($A$3-1)+1,1,31)=$NX$8:$NX$17)*($NY$8:$NY$17))

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
 

Attachments

  • EXCEL ISSUES HELP (Hui edit).xlsm
    109.3 KB · Views: 9
Back
Top