How to automate a cumulative total for multiple entries?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 15 Feb 2007 19:24 GMT

Hi.

I have a spreadsheet where I add multiple entries every day.

Here's the layout:

Date cust # sale COMM

For sake of ease, I add about 6 customers straight down the column.
At the end of the period I'd like to run a simple macro that will show
the total sales and COMMs for every customer. I've been going the
long way about this for awhile by selecting the Sumof symbol and
selecting the fields relevant, but it is becoming very cumbersome as I
have hundreds of entries every time I run this report.

Thanks for any input.

John

Reply to this Message

Tom Ogilvy - 15 Feb 2007 19:43 GMT

why not insert a row above your headers and use

=Sum(C3:C1000)
(as an example - adjust the range to suit).

then you should see the total as you enter the data.

Signature

Regards,
Tom Ogilvy

> Hi.
>
[quoted text clipped - 14 lines]
>
> John

Reply to this Message

John - 16 Feb 2007 03:47 GMT

On Feb 15, 2:43 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
wrote:

> why not insert a row above your headers and use
>
[quoted text clipped - 25 lines]
>
> > John

I apologize for not being more clear. I want to automate the sorting
of all cust. # and then the total sum of COMMs for each cust. #.

Any suggestions?

Thanks

Reply to this Message

Tom Ogilvy - 17 Feb 2007 00:21 GMT

Turn on the macro recorder while you do Data=> sort , then do Data=>Subtotal

Turn off the macro recorder and adjust the recorded code if necessary

Signature

Regards,
Tom Ogilvy

> On Feb 15, 2:43 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 34 lines]
>
> Thanks

Reply to this Message

John - 18 Feb 2007 21:25 GMT

> Turn on the macro recorder while you do Data=> sort , then do Data=>Subtotal
>
[quoted text clipped - 42 lines]
>
> > Thanks

After playing around with this, this appears to do what I need.
Thanks so much Tom! You're a big help!

Reply to this Message

John - 16 Feb 2007 04:02 GMT

On Feb 15, 2:43 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
wrote:

> why not insert a row above your headers and use
>
[quoted text clipped - 25 lines]
>
> > John

Here's a better look at what I'm dealing with:

date    cust #    sales    COMM
12-Jan    1    100    20
12-Jan    2    101    20.2
12-Jan    3    102    20.4
12-Jan    4    103    20.6
12-Jan    5    104    20.8
12-Jan    6    105    21
13-Jan    1    106    21.2
13-Jan    2    107    21.4
13-Jan    3    108    21.6
13-Jan    4    109    21.8
13-Jan    5    110    22
13-Jan    6    111    22.2
14-Jan    1    112    22.4
14-Jan    2    113    22.6
14-Jan    3    114    22.8
14-Jan    4    115    23
14-Jan    5    116    23.2
14-Jan    6    117    23.4
15-Jan    1    118    23.6
15-Jan    2    119    23.8
15-Jan    3    120    24
15-Jan    4    121    24.2
15-Jan    5    122    24.4
15-Jan    6    123    24.6
16-Jan    1    124    24.8
16-Jan    2    125    25
16-Jan    3    126    25.2
16-Jan    4    127    25.4
16-Jan    5    128    25.6

I'd like to be able to sort by cust # and then have the total COMMs
added automatically for each cust. #.

Any suggestions?

Thanks

Reply to this Message

Tom Ogilvy - 17 Feb 2007 00:20 GMT

Use Data=>Subtotal.

Signature

Regards,
Tom Ogilvy

> On Feb 15, 2:43 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 67 lines]
>
> Thanks

Reply to this Message