summarize list of expenses

H

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
 
M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top