Thanks again. To you too.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"T. Valko" wrote in message
...
Congrats, Don!

I has assumed you were already but didn't make it public.

--
Biff
Microsoft Excel MVP


"Don Guillett" wrote in message
...
I was saying that yours was the way to go and "Gracias Senor" for the kind
words.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron Coderre" wrote in message
...
Not me, Don (I don't have a preference)....but DKS wants unique dates.
:)

(BTW......Congratulations on the MVP award)

***********
Regards,
Ron

XL2003, WinXP


"Don Guillett" wrote:

I didn't see that you only wanted UNIQUE dates.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron Coderre" wrote in message
...
I tried scrambling the dates and DeptNames and I still get correct (to
me)
results.

Try this:
Play with my posted example and see if it gives correct results under
those
conditions.
-If yes
...Try putting some of your data into the example and test again.
-If No, then the data is the problem and you'll need to post some
sample
data so we can see what you're dealing with?

Either way, please update us on your progress.

***********
Regards,
Ron

XL2003, WinXP


"DKS" wrote:

Hi Don, Ron

Thanks for your suggestions. I tried them but they did not work. I
forgot
to mention that the data may not always be sorted by date. I hope
that
does
not affect your logic?

To give you extra info on what went wrong: the formula of Don gave
me an
extremely high number. For example: I had data for one calendar
year but
the
result of Don's formula was more than 366. This is not possible
because
there are only 366 unique dates in a year. The formula of Ron
returned
zero
as a result.

Thanks.

"Ron Coderre" wrote:

Try something like this:

With this structure in A1:A16
Date Dept
1/1/2007 A
1/1/2007 A
1/1/2007 A
1/1/2007 B
1/1/2007 C
1/1/2007 C
1/1/2007 C
1/15/2007 A
1/15/2007 B
1/15/2007 C
1/15/2007 C
2/1/2007 A
2/1/2007 A
2/1/2007 B
2/1/2007 B

And...
E1: (StartDate eg: 01/01/2007)
F1: (EndDate eg: 01/31/2007)
G1: (DeptName eg: A)

Then this formula returns the number of unique dates in that range
where the
DeptName matches the name in G1:
=SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B 2:B20=G1)*(A2:A20=E1)*(A2:A20<=F1)*A2:A20,0)))

Using the above example, the formula returns: 2
Dept A appears at least once with 1/1/2007 and 1/15/2007

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"DKS" wrote:

I have a sheet, with each row containing one record. Column A
contains a
date, and column B contains a department. The contents of the
column
A
(date) are either a simple date "dd/mm/yyyy" format or a
timestamp
e.g.
"dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A,
Column B,
Column
A + Column B.

I needed (ideally) a worksheet function that would allow me to
obtain
the
following information:

Based on a START DATE, END DATE and DEPT NAME return to me the
number
of
times I have the DEPT NAME occuring in my data for between the
START
DATE and
END DATE (both dates included) but counting multiple rows for
the
same date
as only 1 record. Thus if I have 27 rows for the same
department
with date
July 15, then it should give me only the value 1.

The time-part in the timestamp format can be ignored. It exists
because of
input data coming from various sources and some sources store
time
and some
do not store time.

How could I do the above with a worksheet function?

Many thanks.