Adding Values Together Based on Business Type

[Sjwdavies]

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?

 

[Andrew Poulsom]

Have you tried a pivot table?

 

[Sandeep Warrier]

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

=sumproduct(--($B$1:$B$100="Business Type"),($A$1:$A$100))

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.

 

[Sjwdavies]

Have you tried a pivot table?

I've not used those before?

 

[Sjwdavies]

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

=sumproduct(--($B$1:$B$100="Business Type"),($A$1:$A$100))

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

 

[Andrew Poulsom]

I've not used those before?

Here is a tutorial on pivot tables:

http://peltiertech.com/Excel/Pivots/pivottables.htm

 

[Sandeep Warrier]

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

=SUMPRODUCT(--($B$1:$B$100="Business Type"),($A$1:$A$100))

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.