Count unique records for a specific date

I have a table in a sheet and I want a formula that will count the unique records. The simplified database is as follows (in a table):

Please Login or Register  to view this content.

As you can see, on the 1-Feb, there are 4 records, but only 2 unique job numbers. There are 3 unique numbers on 2-Feb.

So I would like a forumula that will count the unique numbers in the job# column for a set date. I would have thought sumproduct could do it, but I haven't had any luck. I am open to any suggestion that can return an answer in a cell (ie I don't want to use a pivot table or summarise the table etc).

With your sample data in A1:C10

Please Login or Register  to view this content.

and
E1: a date to match....eg 01-Feb-2010

This regular formula returns the count of unique Job#'s for that date:

Please Login or Register  to view this content.

In the above example, the formula returns: 2

Is that something you can work with?

There are actually quite a few moving parts in this formula:
F1: =SUMPRODUCT(--ISNUMBER(1/((MATCH(B2:B20&$E$1,B2:B20&A2:A20,0)-1+ROW(A$2))=ROW(B2:B20))))

Basically, it looks for each first instance of Job#/Date combination
where the date matches the referenced date.

This section:
MATCH(B2:B20&$E$1,B2:B20&A2:A20,0) finds the matches.

and

this section:
-1+ROW(A$2)

just tweaks the results of the match section so it returns row numbers.

The first part: B2:B20&$E$1
combines each actual Job# with the reference date
in E1 (01-FEB-2010, in the example).
Note: the Excel date serial number for that date is 40210.

These are the Job#/Ref_Date pairs to find:
List_1
1040210
1040210
1040210
1140210
1240210
1240210
1340210
1440210
etc

These are the actual pairs of Job# and Date
from the Col_A/Col_B list:
List_2
1040210
1040210
1040210
1140210
1240211
1240211
1340211
1440211

so, the MATCH function looks for each List_1 item in List_2
and returns the list position.

In the example, the first 3 List_1 items (1040210) are found in the
first position of List_2...but, only the first instance matches
the row number, resulting in TRUE (which is converted to 1).

All non-first-matches result in FALSE (which is converted to 0)

Since those results are used in fraction denominators,
1/1 is a valid number
1/0 is an error (#DIV/0!)

The ISNUMBER function, When applied to the list of returned values (errors or 1's),
returns a 1 for each first instance and a 0 for each non-first-instance.

The SUMPRODUCT function adds up the first-instance values to return their count.

Perhaps not the clearest explanation, but that is definitely an advanced,
NOT a beginner, formula.

I hope that helps.

Avivazuk, Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.