# Sum by Number

#### Tennisguuy

##### Well-known Member
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

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Like this?
Each formula copied down.

Excel Workbook
122100,0000.4400,000400,0002350000500000
135400,0000.5100,000125,0003100000125000
145300,0001200,000250,0005700000375000
153100,0001.5125,000125,000
162250,0001.25200,000100,000
17
SUMIF

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

Excel Workbook
VWXYZAA
122100,0000.4400,000400,000
135400,0000.5100,000125,000
145300,0001200,000250,000
153100,0001.5125,000125,000
162250,0001.25200,000100,000
17
18
19
20Values
21LocationSum of Layer AmountSum of Sold
222350000500000
233100000125000
245700000375000
25Grand Total11500001000000
26
SUMIF

Thanks a lot that worked

Thanks a lot that worked
Glad it helped. Thanks for letting us know.

Replies
8
Views
173
Replies
13
Views
481
Replies
5
Views
145
Replies
2
Views
338
Replies
0
Views
210

1,203,434
Messages
6,055,346
Members
444,781
Latest member
rishivar

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back