View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MyVeryOwnSelf MyVeryOwnSelf is offline
external usenet poster
 
Posts: 213
Default summarize list of expenses

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.