Summing Query

Hi, I would be grateful for any assistance from one or more of the many Excel geniuses on here with something which has been driving me crazy! Copy S/S attached.

Basically, I have a spreadsheet 4,000 lines long which is split by many differing Account Codes, Column C. I have used the Subtotal function to count the total records for each Account.

I am trying to work out a way to further count and display the number of records within each separate Account Code using Column P ( Driver Code ) which equals "Palletline" or "Alternate" show the count for each and the % of the total number of records shown by the Subtotal function. I have tried to use the subtotal function further which just produces erratic results.

I would need an easy way of applying the solution all the way down the spreadsheet with any change in Account Code.

I have attached a smaller version of the actual spreadsheet and noted what I am trying to achieve in Red.

It would be nice to have a grand totals at the end if possible but this is a luxury not a necessity.

Many Thanks

Would the approach in the attached file be more flexible for you? i.e. to have a summary table in a separate sheet. Just add your account codes to column A of the summary sheet and copy the formulae in columns B to E further down as required. No need to sort the data, or to insert subtotals etc.

Note that I've changed the entry in P72 of the main sheet - you had it as "Alternative".

Hope this helps.

Pete

Thanks to you both ( Hemesh & Pete_UK ) for your responses both of which provided a solution. I used the summary sheet suggestion provided by Pete as this was the quickest & easiest way to produce figures for the 300+ customers over 4,000 lines, a very good idea. I wouldnt have come up with either solution unfortunately so many thanks to you both again. Very much appreciated.

Glad to be able to help. I used to do things your way many years ago, until I discovered COUNTIF and SUMIF.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Hi Pete. I have marked the thread as Solved, I was told to do this from the start and I did also try and contact you both directly to thank you but it wouldnt let me. At the bottom left of the posts on my screen I have * Add Reputation & + Reply to Thread. On the R/H side I have Reply, I tried both replys and nothing happens, a circle just spins endlessly on my screen. The only way I can respond is to Quick Reply. I did ask one of the Administrators who couldnt provide a solution.

The icon next to Add Reputation is Report Post - you only use this if you want to bring that post to the attention of the Moderators (for spam, bad language etc.) . The Reply buttons are both on the right. I think the Forum is running a bit slow at the moment, as I have had that spinning circle earlier - I just went to another site and then came back after a while.

You can also contact members directly, either by leaving a visitor message or a Private Message - just click the Members name in the profile section on the left of a post.

Other useful things you can do on the Forum are explained in the FAQ at the top of the screen.

Thanks for the Rep.

Pete