adding occurrences for date range

I've posted this question a couple of times, but it doesn't seem anyone knows
the answer. I am posting it again out of desperation.

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

Try the following...

=SUM((D2:D375=G1)*IF(B2:B375<>"",(B2:B375>=F1)*(B2:B375<=F2),(C2:C375>=F1
)*(C2:C375<=F2)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <[email protected]>,
"Mike" <[email protected]> wrote:

> I've posted this question a couple of times, but it doesn't seem anyone knows
> the answer. I am posting it again out of desperation.
>
> 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