Would like to count cell values based on date criteria in a different range

**sdavison** · 06-22-2013, 04:02 PM

The forum members here have been incredibly helpful in assisting me with this project and I am learning a lot about excel and it's capabilities. I turn once again to the members here with (what I hope to be) the last part of this puzzle I call a workbook! With an attendance template created with the date along row 5 and the data populating in a calendar like format below it, I would like to use a formula to determine vacation days remaining and used based on a "vacation reset" trigger being their anniversary date. A simple count is not going to work because even though a vacation may be input on the sheet, it is not considered used until the date has passed. (Someone told the employees they could change their minds! LOL) I have tried countifs and sumproduct all to no avail. I have attached a sample sheet with Anniversary dates and vacation days per year. On this sheet the H column (Vacation days remaining) should result in 2 for Robin, 11 for Mickey, 3 for Donald and 10 for Peter as I am only counting "v" days used between anniversaries.

I hope I have described what I am trying to make happen (it's clear in my head, but not everybody can fit). Any help, or even a push in the right direction would be greatly appreciated.

"Stuck" in Vegas,

Scott

**FDibbins** · 06-22-2013, 04:19 PM

How do you get 2 for Robin? I count 6. and this formula gives me that too...
=COUNTIFS($I$5:$AK$5,">="&DATE(YEAR(G6)-1,MONTH(G6),DAY(G6)),$I$5:$AK$5,"<="&TODAY(),I6:AK6,"v")

**etaf** · 06-22-2013, 04:25 PM

no longer required

**sdavison** · 06-23-2013, 12:41 AM

You guys are amazing. I'm sorry, you are right and I mis-calculated. This formula is exactly what I was looking for and will be used to put the final touches on this project. (at least until someone asks more of the worksheet). Thank you all for sharing your experience with Excel and helping the community. Excelforum members are the best!!

Scott