DashboardNovice
Member
In the attached spreadsheet, I am trying to use COUNTA on a range. But the range can change from one situation to another (depending on a person's hire date and coverage ending date).
The range is defined in cells J7:K7. The formula containing COUNTA would be in cell L7 (yellow cell).
For example, if Hire Date = 09.14.2015, the starting and ending dates for the range to use would be 10.02.2015 and 04.29.2015. The count here would be 16.
In another example, if the Hire Date = 10.22.2015, the starting and ending dates for the range to use would be 11.13.2015 and 04.29.2015. The count here would be 13.
The Coverage End Date would change only once per year, but when it does change, this would change the value in cell K7.
I know how to use COUNTA, but I just can't figure out how to apply it on a range that can change.
Can anybody offer some insight as to how I should approach this? Would I need an array formula? Is it possible to do this without using an array?
The range is defined in cells J7:K7. The formula containing COUNTA would be in cell L7 (yellow cell).
For example, if Hire Date = 09.14.2015, the starting and ending dates for the range to use would be 10.02.2015 and 04.29.2015. The count here would be 16.
In another example, if the Hire Date = 10.22.2015, the starting and ending dates for the range to use would be 11.13.2015 and 04.29.2015. The count here would be 13.
The Coverage End Date would change only once per year, but when it does change, this would change the value in cell K7.
I know how to use COUNTA, but I just can't figure out how to apply it on a range that can change.
Can anybody offer some insight as to how I should approach this? Would I need an array formula? Is it possible to do this without using an array?
