Horizontal sum with multiple criteria

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):

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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

Not sure to fully understand ...

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

squallleonhart

New Member
Joined
Oct 12, 2010
Messages
14
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.
 

Forum statistics

Threads
1,081,981
Messages
5,362,533
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top