Count unique values in Column A, only if value in Column F falls between two dates

Hi all,

I am hoping someone can help me with developing a formula.

Column A of my spreadsheet contains enquiry numbers that are normally unique, but are occassionally duplicated due to sub-jobs, etc. The enquiry numbers are a numerical sequence, but prefixed with the letters "JE" (eg. JE2905). Column F contains the date the enquiry was received. I am trying to set up a formula that will count the number of unique enquiries for a given period (say 01/08/2014 to 31/08/2014).

I have been able to count the total number of enquiries using the following formula, but I haven't been able to work out how to count unique enquiries only.

=COUNTIFS('Job Register'!F19:F382,">31/07/2014",'Job Register'!F19:F382,"<1/09/2014")

I don't have the option of using a helper column.

I have attached a simplified version of the spreadsheet. The enquiries are recorded in the "Job Register" worksheet. The formula for the above date range would go in cell AA17 of the "Dashboard" worksheet.

I think the formula developed by sktneer in the following thread (http://www.excelforum.com/excel-form...-in-month.html) is along the lines of what I require, but I don't understand it well enough to make the necessary edits.

I would greatly appreciate any assistance I can get.

Sritcho

Thanks Tony. Perfect.

I will use the formula in more than one cell. I figured that once someone had helped me with working out the formula I would be able to edit it as required to use it in other cells. I notice that the way you have written it I can copy and formula paste it straight into the remaining cells in the table.

Thanks again for your help. Greatly appreciated.

Sritcho