Date LookUp
 

I have a list of policies that contains an Effective Date and a Cancellation
Date. I'm trying to do a count by month over the span of a couple of years
of how many policys were active in the given month.

For Example;
May 2007 = 423
June 2007 = 427 and so on.

I need to know if the month I am counting (May 2007) is between the
Effective Date and the Cancellation Date and if it is to include that line
item in the count (423).

Any help will be appreciated.

Date LookUp
 

I'd try something like this

=SUMPRODUCT(--(Sheet1!A1:A10 = Date(YEAR(Begin),Month(Begin),Day(Begin)
),--(Sheet1!A1:A10 = Date(YEAR(End),Month(End),Day(End) ),
(Sheet1!B1:B10))


Where A1:A10 are the columns with dates, Begin is the "effective date", End
is the "Cancellation Date" and B1:B10 contain the count.

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Jordan" wrote:

I have a list of policies that contains an Effective Date and a Cancellation
Date. I'm trying to do a count by month over the span of a couple of years
of how many policys were active in the given month.

For Example;
May 2007 = 423
June 2007 = 427 and so on.

I need to know if the month I am counting (May 2007) is between the
Effective Date and the Cancellation Date and if it is to include that line
item in the count (423).

Any help will be appreciated.

Date LookUp
 

I'm not sure I got the = and <= signs right on the last post and it hasn't
come up yet. Watch for that.

Barb Reinhardt

"Jordan" wrote:

I have a list of policies that contains an Effective Date and a Cancellation
Date. I'm trying to do a count by month over the span of a couple of years
of how many policys were active in the given month.

For Example;
May 2007 = 423
June 2007 = 427 and so on.

I need to know if the month I am counting (May 2007) is between the
Effective Date and the Cancellation Date and if it is to include that line
item in the count (423).

Any help will be appreciated.

Date LookUp
 

Hi,

You can use the following array formula (Ctrl+Shift+Enter). col A has
effective date, col B had expiry date and col C has number of policies

SUM(IF((A2:A4<=A6)*(B2:B4=A6),C2:C4))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Jordan" wrote in message
...
I have a list of policies that contains an Effective Date and a
Cancellation
Date. I'm trying to do a count by month over the span of a couple of
years
of how many policys were active in the given month.

For Example;
May 2007 = 423
June 2007 = 427 and so on.

I need to know if the month I am counting (May 2007) is between the
Effective Date and the Cancellation Date and if it is to include that line
item in the count (423).

Any help will be appreciated.