Good afternoon! I am working on a spreadsheet and have run into a dilemma.
My workbook's datasheet has a column that lists the dates of the visits for each contract. Some contracts will have no visits, some will have only one visit and some will have multiple visits.
On a separate worksheet within the workbook, there is a table that tracks the number of actions taken on a contract. For the datasheet column that tracks Closeout dates, the table uses the formula "=COUNTIFS('Data Sheet'!$AG:$AG,">="&'Milestone Totals'!$M3,'Data Sheet'!$AG:$AG,"<="&'Milestone Totals'!$N3)" to track the number of closeouts performed in July, 2014, where 'Data Sheet'!$AG:$AG is the Closeout column, Milestone Totals'!$M3 is the first day of July, 14, and Totals'!$N3 is the last day of July, 2014. There will only ever be one closeout date entered for each contract. There are three contracts (rows) with July, 2014 dates under Closeout (07/05/2015, 07/13/2015 and 07/28/2015), so the table shows "3" for the period (row)July, 2014.
My question is, how can I modify the formula so that if there are several Visit dates for one contract, the formula counts them properly for the correct period. For example, a contract had a visit on 02/19/2015, 04/29/2015, & 06/01/2015. These three dates are all listed in the same. I want the formula to total the number of visits in February, 2015, April, 2015, and June, 2015, even if the dates are all in the same cell.
I hope I have articulated this well - it makes sense in my head
)
Thanks in advance!
Marcy
My workbook's datasheet has a column that lists the dates of the visits for each contract. Some contracts will have no visits, some will have only one visit and some will have multiple visits.
On a separate worksheet within the workbook, there is a table that tracks the number of actions taken on a contract. For the datasheet column that tracks Closeout dates, the table uses the formula "=COUNTIFS('Data Sheet'!$AG:$AG,">="&'Milestone Totals'!$M3,'Data Sheet'!$AG:$AG,"<="&'Milestone Totals'!$N3)" to track the number of closeouts performed in July, 2014, where 'Data Sheet'!$AG:$AG is the Closeout column, Milestone Totals'!$M3 is the first day of July, 14, and Totals'!$N3 is the last day of July, 2014. There will only ever be one closeout date entered for each contract. There are three contracts (rows) with July, 2014 dates under Closeout (07/05/2015, 07/13/2015 and 07/28/2015), so the table shows "3" for the period (row)July, 2014.
My question is, how can I modify the formula so that if there are several Visit dates for one contract, the formula counts them properly for the correct period. For example, a contract had a visit on 02/19/2015, 04/29/2015, & 06/01/2015. These three dates are all listed in the same. I want the formula to total the number of visits in February, 2015, April, 2015, and June, 2015, even if the dates are all in the same cell.
I hope I have articulated this well - it makes sense in my head
)Thanks in advance!
Marcy
