COUNTIFS between 2 dates - how to calculate for blank dates

My COUNTIFS formula is:

Formula:

Please Login or Register  to view this content.

...where EmpRange and DateRange are named ranges, $A$2:$A$100 (for names, e.g., "Bob" etc.) and $B$2:$B$100 (which contains various dates) ...and DateFrom and DateTo are named cells. When I have an actual date in the named date cells, for example, 9/1/14 for DateFrom, and 9/12/14 for DateTo (USA short format) - the formula works great and returns all the "Bobs" between the specified dates.

But I need the option of leaving either or both of the named date cells BLANK, and for my formula to return the following:

IF DateFrom is blank, but there is a date in DateTo, then calculate the criteria for ALL the dates with "Bill" (following this example) on or before the "To" date (no begin date).

Conversely, if DateTo is BLANK, but DateFrom has a date value, then run the formula calculating from the begin date specified, but no end date (technically, this could be a date in the future, but as I don't think I should have any when I put the workbook in use, so I could use until TODAY if it made a difference)... finally, if both DateFrom and DateTo are blank, return all "Bills" - basically ignoring the dates.

Any suggestions?

Sample1.xlsx

Named Ranges are in Sheet1, the rest in Sheet2.

The formula in question is in Sheet2!E5. With 2 dates in E2 & E3, it works fine. Delete 1 or both dates and it returns "0."

Thanks for looking.

Awesome.

Question on the array formula (I've worked with them but not extensively): once it is entered (with CTRL+SHIFT+ENTER) - can the formula be saved to the file and emailed without the person opening it having to activate it? I guess it can, since I downloaded and opened your sheet with it.

Thanks!