Someone from this group helped me get through the first steps of this process.
I have 2 date columns and a column that identifies a department name. I want
to create a formula that looks for a date range in the first column, if there
is no entry in that column, I want to refer to the second column looking for
that date range. For the records identified I want to count how many times
the department is represented.
First date column is B. Second date column is C. Department name is column D.
I have tried the following:

=SUMPRODUCT((B2:B375=F1)*(B2:B375<=F2)+(Data!$D2: $D532="")*(C2:C532=F1)*(C2:C532<=F2)*(D2:D375=G1) )

F1 = cell contains beginning of date range
F2 = cell contains end of date range
G1 = cell contains Department Name for which I want to count occurrences