Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Lookup | Excel Discussion (Misc queries) | |||
Lookup MIN Date | Excel Worksheet Functions | |||
Lookup MIN Date | Excel Worksheet Functions | |||
Lookup MIN Date | Excel Worksheet Functions | |||
Lookup the date associated with a given value | Excel Discussion (Misc queries) |