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 ,
"Mike" 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