squallleonhart
New Member
- Joined
- Oct 12, 2010
- Messages
- 14
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):
<tbody>
</tbody>
I then have a second sheet where I am going to summarize the data which looks like this:
<tbody>
</tbody>
I want to populate the claims fund, admin, and stop-loss columns for each date range.
Thank you
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