Count Unique based on date

I am trying to count the unique entries in a field. Based on the date it was entered into the sheet.


Column A - Date Entered on to Sheet
Column C - Order No.

There could be more than one instance of an Order Id on the sheet as it could relate to multiple items on the order.

For the first part I am trying to find out a count of unique order ID that was on the sheet before a certain date. There will never be anything on the sheet that is dated after "=today()". to explain the formula I am using below - cell A9 relates to a date e.g 22.08.11
column a on back order summary sheet is the date entered on sheet and column c refers to the order id.

{=COUNT(IF(FREQUENCY(IF('BackOrder Summary'!$A$2:$A$437<Summary!$A$9,MATCH('BackOrder Summary'!$C$2:$C$437,'BackOrder Summary'!$C$2:$C$437,0)),ROW('BackOrder Summary'!$A$2:$A$437)-ROW(Summary!$A$9)+1),))}

as a seperate function i am also looking to capture the count of unique order id's entered onto the sheet that day, i presume i can change the formula where it looks at "..<Summary!$A$9" to "...=Summary!$A$9"

if anyone can help as I am currently not getting the expected result.

Thanks

Dan

So the formula doesn't work? It seems right.

You are confirming the formula with CTRL+SHIFT+ENTER, yes?

If so, maybe a sample spreadsheet would help.

Normally you wouldn't use the part in red

=COUNT(IF(FREQUENCY(IF('BackOrder Summary'!$A$2:$A$437=Summary!$A$9,MATCH('BackOrder Summary'!$C$2:$C$437,'BackOrder Summary'!$C$2:$C$437,0)),ROW('BackOrder Summary'!$A$2:$A$437)-ROW(Summary!$A$9)+1),))

that would be

=COUNT(IF(FREQUENCY(IF('BackOrder Summary'!$A$2:$A$437=Summary!$A$9,MATCH('BackOrder Summary'!$C$2:$C$437,'BackOrder Summary'!$C$2:$C$437,0)),ROW('BackOrder Summary'!$A$2:$A$437)-ROW('BackOrder Summary'!$A$2)+1),))

but in this case that would only cause problems in the last few rows of your range and wouldn't account for a discrepancy of the size you mention......

I didn't catch that, thanks DLL.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You need to add a summing range after that last comma..

e.g.

Please Login or Register  to view this content.

where B2:B437 is the range to sum.

As long as the blanks you talk about are after the main data, then it would be more advisable to use dynamic named range, which automatically expands and contracts as you add or remove data from the main database.

So, in Excel2007, go to the Backorder Summary sheet, and then go to Formulas tab and click Define Name in the Defined Names section.

Enter a name like MyData and then refers to:

Please Login or Register  to view this content.

Click Ok

Then use formula like:

Please Login or Register  to view this content.

Where the non-zero number in the INDEX() functions represents the column number of interest in the named range... so 1 is A, 10 is J and 2 is B (summing range).