Newbie questions regarding dates, thanks in advance for your assistance!

[kevin223]

My office manager provides me an excel report (sample below) where we keep a running total of all business that a particular Agent writes, divided into three categories, Life, LTC, and Annuities. There are different subtypes of policies shown in the policy code column, the amount of premium the customer is paying, the amount of face amount of the policy, the amount of commission (equalizer) and other information.

What I need is a report to show information for the business written during the current month vs. the prior month, also 1st quarter vs. 2nd, 3rd, and 4th Quarters, and 2009 vs 08, 07, 06, and 05. These should all obviously need to reference the date column, but I do not know how to do this, sigh.

Any help would be greatly appreciated. If you know of a thread that directly refers to this or other internet resource (youtube, training, etc.) just point me there. I have already searched to no avail. Thanks again!

Sample
Agent Name Policy Type Date Written Client Name Council Policy Code Premium Face Amount Equalizer Amount A-I Status
Byron Agent Life 1/8/2009 Last Name, First Name 10766 811 $2,771.80 $10,000 $74.84 Not Taken
Byron Agent Life 1/8/2009 Last Name, First Name 10766 801 $1,524.00 $100,000 $868.68 Released
Byron Agent Life 1/8/2009 Last Name, First Name 10766 811 $6,406.00 $22,357 $172.96 Released
Byron Agent Life 1/8/2009 Last Name, First Name 10766 801 $798.00 $50,000 $454.86 Released
Byron Agent LTC 1/13/2009 Last Name, First Name 418 NHC01 $1,097.88 $0 $461.11 Postponed
Byron Agent LTC 1/13/2009 Last Name, First Name 418 NHC01 $1,157.64 $0 $486.21 Released
Byron Agent Life 1/15/2009 Last Name, First Name 8240 852 $1,467.48 $199,650 $440.24 1 Declined
Byron Agent Life 1/15/2009 Last Name, First Name 8240 801 $178.20 $15,000 $101.57 Released
Byron Agent Life 1/15/2009 Last Name, First Name 8240 852 $1,132.08 $199,650 $305.66 Unfulfilled
Byron Agent Annuity 1/20/2009 Last Name, First Name 418 960 $0.00 $45,337 $965.39 Pending
Byron Agent Annuity 1/22/2009 Last Name, First Name 367 960 $0.00 $10,000 $400.00 Released

 

[jbeaucaire]

One set of formulas you can start with:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Agent Name	Policy Type	Date Written	Client Name	Council	Policy Code	Premium	Face Amount	Equalizer Amount	A-I	Status
2	Byron Agent	Life	1/8/2009	Last Name, First Name	10766	811	$2,771.80	$10,000	$74.84		Not Taken
3	Byron Agent	Life	1/8/2009	Last Name, First Name	10766	801	$1,524.00	$100,000	$868.68		Released
4	Byron Agent	Life	1/8/2009	Last Name, First Name	10766	811	$6,406.00	$22,357	$172.96		Released
5	Byron Agent	Life	1/8/2009	Last Name, First Name	10766	801	$798.00	$50,000	$454.86		Released
6	Byron Agent	LTC	1/13/2009	Last Name, First Name	418	NHC01	$1,097.88	$0	$461.11		Postponed
7	Byron Agent	LTC	1/13/2009	Last Name, First Name	418	NHC01	$1,157.64	$0	$486.21		Released
8	Byron Agent	Life	1/15/2009	Last Name, First Name	8240	852	$1,467.48	$199,650	$440.24	1	Declined
9	Byron Agent	Life	1/15/2009	Last Name, First Name	8240	801	$178.20	$15,000	$101.57		Released
10	Byron Agent	Life	1/15/2009	Last Name, First Name	8240	852	$1,132.08	$199,650	$305.66		Unfulfilled
11	Byron Agent	Annuity	1/20/2009	Last Name, First Name	418	960	$0.00	$45,337	$965.39		Pending
12	Byron Agent	Annuity	1/22/2009	Last Name, First Name	367	960	$0.00	$10,000	$400.00		Released
Sheet2

Excel Workbook
	M	N	O	P	Q	R	S	T	U	V	W
1	Agent	Current Month	Prior Month	Q1	Q2	Q3	Q4	2009	2008	2007	2006
2	Byron Agent	0	0	$651,994.00	$ -	$ -	$ -	$651,994.00	$ -	$ -	$ -
Sheet2

 

[kevin223]

Thank you for the reply, this will be very helpful. I will play with it and see what I get. From the looks of the formulas below, they are all referencing the "Face Amount" Column. If I wanted numbers for premium and Equalizer amounts, I would just need to change the Column H references to G and I respectfully, correct?

Not to be greedy, but is there a way to only have the values appear if they are Life (Column B)? I guess that would be an IF statement? I dunno. If I could know that, then I could repeat it for "Annuity" and "LTC" in separate areas of the same report.

I tried to do this with pivot tables but couldn't figure out how to filter by a date range that didn't include ALOT of clicking.

Thanks again for reading and helping!

 

[jbeaucaire]

The cool thing about SUMPRODUCT() is that if you need to add more restrictions, you just add another section WITHIN the formula itself adding the new column and new criteria. Take that N2 formula for instance. It currently only has filters in it for the date and the name. But you can add the Type filter right in there:

=SUMPRODUCT(--($A$2:$A$100=$M2), --($B$2:$B$100=
Life"), --($C$2:$C$100>=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), $H$2:$H$100)