Horizontal sum with multiple criteria

[squallleonhart]

I've been driving myself crazy this morning trying to make this work, but I know someone here can point me in the right direction.
I have some premiums collected sorted by month received on the horizontal axis and three breakdowns for the premium in column D. I would like to sum these for each group ID and premium component across 12 month periods. Here is a snapshot of what the data looks like (blank spaces indicate no premium received):

Group ID	Renewal Date	Premium Component	10/1/2017	11/1/2017	12/1/2017	1/1/2018	2/1/2018	3/1/2018	4/1/2018	5/1/2018	6/1/2018	7/1/2018	8/1/2018	9/1/2018	10/1/2018	11/1/2018	12/1/2018	1/1/2019	2/1/2019	3/1/2019	4/1/2019
34168000	1201	Claims Fund															8161.04	8161.04	8161.04	8386.88	7425.95
34168000	1201	Admin															3136.77	3136.77	3136.77	3241.63	2884.73
34168000	1201	Stop-Loss															10955.45	10955.45	10955.45	11368.66	10154.47
34310000	1201	Claims Fund			6077.15	5887.57	6022.02	6395.01	7499.92	6675.45	6675.45	6675.45	7350.12	6934.94	7047.7	4638.55	8068.1	7267.1	8171.03	7896.09	7707.56
34310000	1201	Admin			2446.2	2369.89	2424.01	2574.15	3018.89	2687.03	2687.03	2687.03	2958.6	2791.48	2836.87	1867.12	4093.43	3771.01	4189.24	4091.39	3987.27
34310000	1201	Stop-Loss			11046.86	10702.26	10946.66	11624.67	13633.13	12134.44	12134.44	12134.44	13360.83	12606.13	12811.11	8431.77	13510.51	12054.49	13293.58	13067.06	12722.12
34363000	0101	Claims Fund																	6923.62	3566.79	3566.79
34363000	0101	Admin																	4162.34	2158.43	2158.43
34363000	0101	Stop-Loss																	10795.07	5621.44	5621.44
34461000	0101	Claims Fund																	4863.94	2431.97	2431.97
34461000	0101	Admin																	2505.66	1252.83	1252.83
34461000	0101	Stop-Loss																	5802.56	2901.28	2901.28
34700000	0101	Claims Fund																4112.77	4112.77	4112.77	4112.77
34700000	0101	Admin																2135.54	2135.54	2135.54	2135.54
34700000	0101	Stop-Loss																4978.14	4978.14	4978.14	4978.14
34706000	0101	Claims Fund																1685.09	1685.09	1685.09	1685.09
34706000	0101	Admin																1091.56	1091.56	1091.56	1091.56
34706000	0101	Stop-Loss																2961.64	2961.64	2961.64	2961.64
34765000	0101	Claims Fund																20754.31	20870.15	18990.99	19274.15
34765000	0101	Admin																12728.99	12801.11	11631.13	11807.43
34765000	0101	Stop-Loss																36180.23	36386.98	33033.03	33538.42
34805000	0101	Claims Fund																8526.03	8526.03	9457.5	8836.52
34805000	0101	Admin																3898.47	3898.47	4324.38	4040.44
34805000	0101	Stop-Loss																10171.74	10171.74	11283	10542.16
34872000	1201	Claims Fund				10249.36	6287.96	6000.3	5459.7	5459.7	5450.43	5597.71	5657.03	5553.73	5547.66	5939.45
34872000	1201	Admin				4508.56	2783.33	2655.59	2407.88	2407.88	2381.15	2435.98	2472.99	2427.07	2425.7	2579.91
34872000	1201	Stop-Loss				21313.66	13197.47	12590.66	11397.16	11397.16	11219.3	11455.87	11655.64	11437.47	11433.96	12121.76

<tbody>
</tbody>

I then have a second sheet where I am going to summarize the data which looks like this:

Group ID	Group Name	Plan Year Beginning	Policy Year End	Claims Fund	Admin	Stop-Loss
34168000	a	12/1/2018	11/1/2019
34310000	b	12/1/2017	11/1/2018
34310000	b	12/1/2018	11/1/2019
34363000	c	1/1/2019	12/1/2019
34461000	d	1/1/2019	12/1/2019
34700000	e	1/1/2019	12/1/2019
34706000	f	1/1/2019	12/1/2019
34765000	g	1/1/2019	12/1/2019
34805000	h	1/1/2019	12/1/2019
34872000	i	12/1/2017	11/1/2018

<tbody>
</tbody>

I want to populate the claims fund, admin, and stop-loss columns for each date range.

Thank you

 

[James006]

Hello,

Not sure to fully understand ...

But have you tried to Insert a Pivot Table ... to groups your data just in 5 seconds ...

 

[squallleonhart]

I need a sum for each date range listed in the second part of my post. It is not immediately apparent to me how I would instruct the pivot table to do sums across the given ranges. For example, group 34310000 would need 2 entries, one for the date range 12/1/2017 to 11/1/2018 and one from 12/1/2018 to 11/1/2019. I would like to be able to do this without a bunch of manipulations to the data at the top of my post.