Adding totals 1

[Chr1sUK]

I am busy with a spreadsheet that shows all motor insurance claims for a particular company.

Each claim has a claim code.

I have inserted a worksheet to show the breakdown of claims by claim code, and the total amount of money that has been paid out as a result of such claims.

To total up the total number of claims for a particular code, I have used countif, which is fine.

However, I then want to be able to include the total in terms of money for every claim under that code, (the figure is a few cells to the left of the claim code cell) but I cant think of how to do it.

Any suggestions?

TIA.

 

[Molby]

Pivot table looks like the best way to go.

 

[Chr1sUK]

I see...

Can you give me a little more info on that please?

Would SUMIF not work?

The codes are in column S, the totals are in column Q.

For example, I want the totals in column Q to be added when the value in column S = 1.

How would I do that in this case?

 

[Tiglet]

SUMIF would work and would have the benefit of being dynamic.

A pivot table has the benefit of looking pretty and will capture all of the claim numbers. It would summarise all of the claim codes and all of their total claims in one go. There is a pivot table wizard and to help you get going, have a read of the help. I would recommend that you move the column of values to the right of the column of claim numbers

Tiglet

Living on Earth is expensive, but it does include a free trip around the sun every year

 

[Chr1sUK]

Thanks,

How would I do the SUMIF?

 

[SkipVought]

Chr1sUK,

There are a number of options in Excel for reporting. PivotTable has already been suggested.

I also suggest Subtotal via Data/Subtotals... The only pre-requisite is to have your table of data sorted by the summary columns. Multiple summaries (sum, count, average, etc) can be done on one table as well.

Neither of these solutions requires spreadsheet formulas.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Chr1sUK]

Not having much luck with the above mentioned methods!

I think I will just use SUMIF.

I have tried, but getting results that are way off.

A little bit of help with SUMIF would be appreciated....newbie terms please

Thankyouplease.

 

[SkipVought]

Please post a sample of your data table, headings and all.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Chr1sUK]

OK here is a sample, I have edited it since it is private company data.

Total Veh. Reg Code Driver Age
456 AB 98 DRIVER1 67
0 BC 98 DRIVER2 28
0 CD 98 DRIVER3 41
100 DE 98 DRIVER4 55
200 EF 98 DRIVER5 49
0 FG 98 DRIVER6 55
300 GH 13 DRIVER7 30
305 HI 98 DRIVER8 38

For each occurance of the CODE, I want the TOTAL to be added up, for that particular code, to give an overall total, which is on a seperate sheet that I have created to show the claims by incident number.

Thanks.

 

[Chr1sUK]

ooh...that didnt come out so well...hopefully this will work.

Total Veh. Reg Code Driver Age
456 AB 98 DRIVER1 67
0 BC 98 DRIVER2 28
0 CD 98 DRIVER3 41
100 DE 98 DRIVER4 55
200 EF 98 DRIVER5 49
0 FG 98 DRIVER6 55
300 GH 13 DRIVER7 30
305 HI 98 DRIVER8 38

 

[KenWright]

Chris, you have soooooooooooooooo got to look at the Pivot table route for this kind of data. It will give you an enormous amount of analytical power, and let you see the data in ways you hadn't considered. I'll happily knock you up a quick dummy file based on your example data and send it you if you want.

If the data is clean, as in your example, then I could LITERALLY create a Pivot table from that with meaningful reports, in circa 30 seconds.

Select all the daat including headings (Every column of dat must have a heading), hit Data / Pivot Table and Pivot Chart report, then hit Next / Next / Finish.

Drag Code to the left (ROW FIELD) and Total to the centre (DATA FIELD). You now have your report. If it makes sense then you can also drag fields to the top (COLUMN FIELD)

You can then drag more/less fields in/out around etc on that report and it will change before your eyes.

LITERALLY 30 SECONDS!!!!!!!!!!

It is the most powerful feature Excel has, and will pay enormous dividends for you if you have to work with this kind of data.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

DITTO, DITTO, DITTO!!!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[KenWright]

I don't believe you meant DITTO Skip, I'm sure you meant to say 29 seconds

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

I'm feeling charitable today...

especially to a colleague

across "The Pond" ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Chr1sUK]

Thanks for that, I will take a look at a pivot table tomorrow.

I tried one earlier, and it wasnt adding up the totals....I must have been doing something wrong!

I got it sorted with the SUMIF function after trial and error, its working now, and I have learned how to use a new function

Thanks for the advice!

Have a pink star

 

[KenWright]

If it wasn't adding up the totals it may well have been counting them, in which case you have blanks in your data somewhere, OR, some data may be in as text as opposed to numeric. When either of these is the case, your data will default to a count instead of a sum, but if this happens you can right click on one of the data counts, choose fields settings and then where it says summarize by, click on SUM as opposed to COUNT.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Davidprince]

You could use the offset function in a formula to get the data that is a 'few cells away' or a second option may be to define the claims as a group and use the lookup functions to get your totals.
Davidprince