Counting zeros in an array based on variable dates

[bhenderson]

Hello,

I have attached a sample file with a few rows of data. I am trying to create code that will go row by row and count the zeros after the hire date for an employee as well as the zeros before the terminated date. The hire date and termination date are listed in column AV and AW.
(Note: Hire date and termination date do not, necessarily, correlate with the first or last non-zero number.)

My goal is to produce what I manually counted in columns AX and AY. I have highlighted the zeros that would be counted to hopefully make it clear what I need to count. In a nutshell I need to have the code:
1. Check the hire date in the row
2. Match the hire date to the closest date in row 2 (closest month would be fine)
3. Begin counting 0's from this date to the end of the row (Grand Total column)
4. Check the termination date in the row
5. Match the termination date to the closest date in row 2 (closest month would be fine)
6. Begin counting 0's that are from this date and earlier until the beginning of the row (Row Labels column)
7. Loop to next row

There will definitely be some zeros counted twice in each row but that is okay. I tried to put in some of my own code but it is only a start and way off. I am no Excel guru.

Thank you for your help,
Ben

[p24leclerc]

With a COUNTIFS function, you should be able to get what you're looking for.

As in

Please Login or Register  to view this content.

See attached file.

You'll have to work on the termination date function as I was not sure how to handle it. If there is no termination date, how do you count the 0's? In this condition, the actual function in the file do not count any 0's.

Regards
Pierre

[bhenderson]

Right on! I didnt realize you could get a countif to do that - awesome. I just added an IF funciton on to the termination part and it works great! You rock, rep up.