Automatic collation of information in Excel

Microsoft Microsoft excel 2007 open lice...
August 31, 2010 at 00:44:00
Specs: Windows 7
 I am working on a budget report where I have a P&L sheet with Expense and Income Text data in Column B and their corresponding amounts in columns C (Income) & D (Expense). On another sheet I want Excel to look up all the itemised costs/incomes and then transpose the cumulative sum of these to show the costs spent or received from each item.For instance: On my P&L I have the following:Date, Income/Expense Detail, Income Amount, Expense Amount8/25/2010, Salary, \$250, .8/25/2010, Shopping, ,\$35.508/29/2010, Salary, \$150, .etcNow on the second sheet I want to be able to search down the Income/Expense Detail column and find "Salary" and if it has this value, then to take the value in column C (Income Amount) and add it to the next "Salary" amount found and then paste into the Summary page, in a section called Cumulative Salary. I then want to do the same for Shopping, and Rent etc.Can someone please help with which formula I would use for this?

See More: Automatic collation of information in Excel

#1
August 31, 2010 at 16:33:50
 Hi,Assuming that your source data is in a worksheet named "P&L" and that the summary is in a worksheet named "Summary"and on the P&L sheet descriptions are in column B, Income is in column C and Expenditure is in column Dand assuming that your data on the P&L sheet extends from row 2 to row 150do this:On the Summary worksheet place headings in row 1: A1 is Description, B1 is Income and C1 is ExpenseThen in column A starting at cell A2 enter the descriptions:SalaryShoppingetc. Spelling and capitalization must match exactly what is used in the P&L sheet.In cell B2 enter this formula:=SUMIF('P&L'!\$B\$2:\$B\$150,\$A2,'P&L'!C\$2:C\$150)Drag the formula to extend it to cell C2Now select cells B2 and C2 and drag them down alongside all the descriptions in column A.Using your example, B2 will display 400 and C3 will display 35.5Format columns B & C to display a currency format.If you don't like the zeros in the income column for expenditures and vice versa, use this formula in B2:=IF(SUMIF('P&L'!\$B\$2:\$B\$150,\$A2,'P&L'!C\$2:C\$150)=0,"",SUMIF('P&L'!\$B\$2:\$B\$150,\$A2,'P&L'!C\$2:C\$150))drag and extend as before.Hope this is what you were looking for.Regards

Report •

#2
August 31, 2010 at 23:06:02
 Thanks so much Humar, from your suggestion I have been able to adapt the formula to fit my initial needs.Further in the 'summary' sheet I want to sum the items in the 'P&L' sheet into months, so effectively need to build upon the first formula to incorporate the detection of a month as well.On the 'P&L' sheet in A1 is Date item, and obviously under that in A2 is the first date: eg. 28-Aug-2010. A3 may then be 30-Aug-2010, followed by A4, 1-Sept-2010.What I want to do is for the formula to check the date column, and if it satisfies the month criteria, then to do the secondary SUMIF calculation you offered to me previously.So in this way I will be able to have on my 'Summary' sheet the months in cells B1 (August), C1 (September), D1 (October) etc. then in A1 Descriptions, followed by in A2, Salary, A3 Shopping etc. and be able to see how much I have spent each month on each line item from my entries into the P&L sheet.I hope I have made this clear enough.Thanks again for your support.

Report •

#3
September 1, 2010 at 05:26:55