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 Amount
8/25/2010, Salary, $250, .
8/25/2010, Shopping, ,$35.50
8/29/2010, Salary, $150, .

Now 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

Report •

August 31, 2010 at 16:33:50

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 D
and assuming that your data on the P&L sheet extends from row 2 to row 150
do this:
On the Summary worksheet place headings in row 1:
A1 is Description, B1 is Income and C1 is Expense
Then in column A starting at cell A2 enter the descriptions:
etc. Spelling and capitalization must match exactly what is used in the P&L sheet.
In cell B2 enter this formula:
Drag the formula to extend it to cell C2
Now 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.5

Format 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:
drag and extend as before.

Hope this is what you were looking for.


Report •

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 •

September 1, 2010 at 05:26:55

With dates in column A, description in column B, income in C and Expenditure in D on the P&L worksheet:

On the summary worksheet:
Cell A2 "Description"
Cell B2 "Income"
Cell C2 "Expense"
Cell D1 "Start date"
Cell D2 "End date"

Then select cells E1 to F2 and format them in your preferred date format
In cell E1 enter the first date for your data, say 01-Jan-2010
In cell E2 enter this formula: =EMONTH(E1,0) and E2 will display the last day of the month of the date above it
[see note at end of this post about EOMONTH if you are using Excel 2003 or earlier]
In cell F1 enter = E2+1 which will display the first day of the next month
In cell F2 enter =EMONTH(F1,0)

Now select F1 and F2 and drag them to extend the formulas to column P
P1 will show 01 December and P2 31 December.
The formulas will handle leap years as well.

In column A enter the various descriptions.

Use columns B and C as before for totals of each income/expense item if required.

In cell E3 enter this formula: =SUMPRODUCT(('P&L'!$B$2:$B$150=$A3)*('P&L'!$A$2:$A$150>=E$1)*('P&L'!$A$2:$A$150<=E$2)*('P&L'!$D$2:$D$150))
Note the position of the $ signs - they are crucial to extending this formula.
The last row with data is row 150 on the P&L worksheet. Change this to include the maximum rows you are likely to use. Including empty cells will not affect the results, and save you having to change the range of used cells as new data is added.
Drag the formula down to extend it for as many rows as there are descriptions in column B.
Now edit the formulas on Income rows to: =SUMPRODUCT(('P&L'!$B$2:$B$150=$A3)*('P&L'!$A$2:$A$150>=E$1)*('P&L'!$A$2:$A$150<=E$2)*('P&L'!$C$2:$C$150))

Now select all the cells with formulas in column E, starting at E3
and drag them across to column P

You should now have income and expenditure for each item for each date period.

(To make data range management easier, you could use named ranges for the data on the P&L sheet.
I selected cells A2 to A150 and named them PLDate
Cells B2 to B150 were named PLDesc
and the two income/expenditure columns were named PLInc and PLExp

The formula in E3 on the Summary worksheet [if this was an income row] becomes:
E4 - an expenditure row - becomes:

The same approach to summing the data for months can be used for quarterly or annual expenditure.
Use the same formulas but put them under different data ranges.

Note about EOMONTH()
The EOMONTH function is standard in Excel 2007 and 2010
In Excel 2003 and earlier, the 'Analysis Toolpak' has to be activated. It is installed, but not active.
In Excel 2003, click on the Tools menu item, then select Add-Ins and from the Add-In dialog box check the check-box next to AnalysisToolpak and click OK. EOMONTH will now be available.

PS I re-read you post and see that you don't want the original totals in columns B & C.
Just follow these instructions and when it's working you can delete columns B & C, and the formulas will adjust appropriately.

Report •

Related Solutions

Ask Question