summarize list of expenses

[Harold Good]

Hi,

Each quarter I have a list of several hundred rows of expenses, each with a
Project Number, Account Name, and Amount in their respective columns.

I would like to summarize these for our finance office to import into their
finance software. Lets say there are 200 rows, each specifying from a
possible10 Project Numbers in Col A and a possible 25 Account Names in Col
B. The amount is in Col C.

I'd like to use Sumproduct to bring these all into a summarized list. It
should look something like this:
Project Account Amount
Number Name
====== ============ ======
124 Banking Charges $125
124 Office Expenses $325
124 Postage $340
127 Postage $230
127 Rent $5600
128 Office Expenses $1290
128 Salary $4000
128 Travel $19200
128 Gas $120
131 Salary $3500
135 Gas $210

Is there a way to do this with Sumproduct? I know how to do it if I can
specify a specific Project Number and Account Name, but how do I have it do
them all?

Thanks,
Harold

 

[MyVeryOwnSelf]

Each quarter I have a list of several hundred rows of expenses, each

with a Project Number, Account Name, and Amount in their respective
columns.

I would like to summarize these for our finance office to import into
their finance software. Lets say there are 200 rows, each specifying
from a possible10 Project Numbers in Col A and a possible 25 Account
Names in Col B. The amount is in Col C.

I'd like to use Sumproduct to bring these all into a summarized list.
It should look something like this:
Project Account Amount
Number Name
====== ============ ======
124 Banking Charges $125
124 Office Expenses $325
124 Postage $340
127 Postage $230
127 Rent $5600
128 Office Expenses $1290
128 Salary $4000
128 Travel $19200
128 Gas $120
131 Salary $3500
135 Gas $210

Is there a way to do this with Sumproduct? ...

Here's one way. If the original list is in Sheet1, and if Sheet2 has
columns A and B filled in with the valid combinations, put something like
this in Sheet2!C1 and copy down:

=SUMPRODUCT((Sheet1!$A$1:$A$900=A1)*
(Sheet1!$B$1:$B$900=B1)*(Sheet1!$C$1:$C$900))

To detect data errors, it's a good idea to check that the sum of column C
is the same on both sheets.

 

[Herbert Seidenberg]

Try Pivot Table
No formulas needed:
http://www.freefilehosting.net/download/3fj6k