Unique Number of Values based on Date Columns

I have a 4 column Spreadsheet

Column A is a Name
Column B is a Date
Column C is a Date
Column D,E have two specific dates one in D2 and one in E2 (start and end)



I need to know if there is a way that I can have Excel look through all of the rows (could be thousands) and if the date in column B is greater than the date I have entered in D2 AND the date that I have in column C is less than the date that I have in E2 tell me how many unique Names I have in column A. End result of the formula will be an integer value


Sample Worksheet is attached.

Thanks for such a quick response.

Not sure I fully understand what is going on with the formula... I've substituted the appropriate values for the data in my larger spreadsheet. I end up getting a single name instead of what I would expect which would be a number of 1, 2 or 3 - (there are only 3 possibilities for name).

In that last COUNTIF what is the reference to F$1:F$1... also can I do the A$2:A$537 (etc.) to actually not have the upper limit... the spreadsheet continually adds rows... I don't want to have to adjust the formula for added rows.

Direct Paste of this formula into my example spreadsheet produces "Another Name" as the result.

I dont think that I made my request clear... I really wasn't looking for the names themselves but actually just the count of unique names. I would expect this to be an integer and for the example worksheet the result should be a 2.

Helper column D is used. It can be Hidden also.
in D2, ten drag down.

=IF(AND(B2>$F$2,C2<$G$2),A2,"")

In H2, ARRAY formula

=SUM(1/(COUNTIF(D2:D537,D2:D537)))-1

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.