Sum by Number

[Tennisguuy]

I have a spreadsheet where I want to sum certain values by location but the location number will vary and not be in sequential order. The following is the headings I have in the column. The headings start in column W11:W15.

Location Layer Amount Layer Rate Ceded Premium Sold
2 100,000 .40 400,000 400,000
5 400,000 .50 100,000 125,000
5 300,000 1.00 200,000 250,000
3 100,000 1.50 125,000 125,000
2 250,000 1.25 200,000 100,000

In one cell I want to sum the location by the layer amount and in another cell I want to sum the location by the sold amount. In the above example location #2 layer amount would be 350,000 and the sold would be 500,000

 

[Peter_SSs]

Like this?
Each formula copied down.

Excel Workbook
	W	X	Y	Z	AA	AB	AC	AD	AE
11	Location	Layer Amount	Layer Rate	Ceded Premium	Sold		Location	Layer Amount	Sold
12	2	100,000	0.4	400,000	400,000		2	350000	500000
13	5	400,000	0.5	100,000	125,000		3	100000	125000
14	5	300,000	1	200,000	250,000		5	700000	375000
15	3	100,000	1.5	125,000	125,000
16	2	250,000	1.25	200,000	100,000
17
SUMIF

 

[Peter_SSs]

You could also use Excel's built-in Pivot Table feature for this.

Excel Workbook
	V	W	X	Y	Z	AA
11		Location	Layer Amount	Layer Rate	Ceded Premium	Sold
12		2	100,000	0.4	400,000	400,000
13		5	400,000	0.5	100,000	125,000
14		5	300,000	1	200,000	250,000
15		3	100,000	1.5	125,000	125,000
16		2	250,000	1.25	200,000	100,000
17
18
19
20			Values
21		Location	Sum of Layer Amount	Sum of Sold
22		2	350000	500000
23		3	100000	125000
24		5	700000	375000
25		Grand Total	1150000	1000000
26
SUMIF

 

[Tennisguuy]

Thanks a lot that worked

 

[Peter_SSs]

Thanks a lot that worked

Glad it helped. Thanks for letting us know.