#### Sjwdavies

##### New Member
Hi there. I work for an insurance company and have previously been asked to produce a report showing total premium per business type. Last time I did this by scrolling down the list of data manually, then copying the business type with an auto sum in the column next to it.

Here's the sort of data I have:

319.56 BAKERS
260 BAKERS
407.81 BAKERS
360 BEAUTY SALON
280.15 BEAUTY SALON
0 BEAUTY SALON
625.44 BEDS RETAIL
293.61 BEDS RETAIL
490.66 BEDS RETAIL
403.92 BRIDAL WEAR
333.32 BRIDAL WEAR
390.42 BUTCHERS
640 CAFE
224.76 CAFE
390 CAFE
0 CAFE
0 CAFE
0 CAFE
460.87 CAFE
262.5 CAFE
557.82 CAFE
543.48 CAFE
262.5 CAFE
270 CARDSHOP
0 CARDSHOP
0 CARDSHOP
219.05 CARDSHOP
356.4 CLOTHES SHOP
260.63 CLOTHES SHOP
273.35 CLOTHES SHOP
265.08 CLOTHES SHOP
288.6 COFFEE SHOP
260.95 COFFEE SHOP

I've tried to write a macro that cycles through this to do it automatically but haven't been successful.... Can anyone help me out?

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Have you tried a pivot table?

If Column A has the premiums and Column B has business types, then you can use

or, you can use advanced filter to get unique values from column B into column C. Suppose you have 10 unique businesses, then, in D1 type in

=sumproduct(--(\$D\$1:\$D\$10=C1),(\$A\$1:\$A\$100)) and drag down till D10.

If Column A has the premiums and Column B has business types, then you can use

or, you can use advanced filter to get unique values from column B into column C. Suppose you have 10 unique businesses, then, in D1 type in

=sumproduct(--(\$D\$1:\$D\$10=C1),(\$A\$1:\$A\$100)) and drag down till D10.

I'm not quite sure what you mean?

Sorry, treat me as a novice here

I'm not quite sure what you mean?

Sorry, treat me as a novice here

Hello,

If Column A in your Excel Workbook has all Premiums & Column B has all all Business Types then in C1 you can put

Taking one example from the sample data you provided, C1 could be

=SUMPRODUCT(--(\$B\$1:\$B\$100="Bakers"),(\$A\$1:\$A\$100))

You need to change the range (in red) to match your actual data. I have assumed that you have 100 rows of data.

The disadvantage of this formula is that you will have to type in the Business Type names every time you want to check it.

To avoid this go to Data -> Filter -> Advanced Filter.

If you get a message box, click OK. Select the option that says "Copy to another location". In the "List Range" select the range that has your Business Type. In the "Copy To" select cell C1. Check the option that says "Unique Records Only" and click on OK.

Now in column C you would have only unique values of Business Type. In cell D1 put the formula

=SUMPRODUCT(--(\$B\$1:\$B\$100=C1),(\$A\$1:\$A\$100))

Again as before change the range (in red) to match your data

Assuming there are 10 unique Business Types, select the range D1:D10 and press Ctrl+D. If you see the same values in all cells, press F9. You should have your result.

Hope this helps.

Regards,
Sandeep.

1,203,081
Messages
6,053,414
Members
444,662
Latest member
AaronPMH

### 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