excel 2007 - tally data records into sub totals

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

robr - 09 Apr 2007 15:53 GMT

I have data that looks something like this

account#, invoice amount
1, 50
1, 70
1, 80
2, 10
2, 50
2, 52
2, 50
3, 10
4, 30

I need a way to combine all the invoice totals for each account so I
have results like this
1, 200
2, 162
3, 10
4, 30

Any suggestions on how to go about doing this? I thought I had done
something similar to this with filters
before in Excel 2003, but I can't figure out how I did it. Thanks!

Reply to this Message

Peo Sjoblom - 09 Apr 2007 16:17 GMT

You can use subtotal or a pivot table
in the pivot put the account numbers in the row field and the invoice amount
in the data, in subtotal use at each change in account numbers, sum and add
subtotal to invoice amount. The subtotal function as opposed to the pivot
tables needs the account numbers to be grouped together (sorted) before you
can apply it

Signature

Regards,

Peo Sjoblom

>I have data that looks something like this
>
[quoted text clipped - 19 lines]
> something similar to this with filters
> before in Excel 2003, but I can't figure out how I did it. Thanks!

Reply to this Message

robr - 09 Apr 2007 16:26 GMT

Thanks, I actually just came back to report I figured out how to do
what I needed with the subtotal wizard and the hide detail option.

> You can use subtotal or a pivot table
> in the pivot put the account numbers in the row field and the invoice amount
[quoted text clipped - 31 lines]
> > something similar to this with filters
> > before in Excel 2003, but I can't figure out how I did it. Thanks!

Reply to this Message