Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summarize list of expenses
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summarize list of expenses
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
spreadsheet - expenses | New Users to Excel | |||
How to summarize the first 4 numbers greater than 0 from a list. | Excel Worksheet Functions | |||
Summarize Out of Stock List | Excel Worksheet Functions | |||
HOW DO I SUMMARIZE A LIST OF TEXT, I WANT ONE A TO SHOW UP NOT 10 | Excel Worksheet Functions | |||
How do I summarize totals for a list of values in Excel? | Excel Worksheet Functions |