Link to home
Start Free TrialLog in
Avatar of WeThotUWasAToad
WeThotUWasAToad

asked on

Excel formula to create typical Profit & Loss Statement totals

Hello,

What formula in Excel will extract totals from a standard checkbook account to create the values in a typical Profit & Loss Statement?

Suppose a spreadsheet contains a ledger showing dated and categorized payments for some number of years — in a form similar to a standard checking account as shown here:

User generated imageWhat formula would return the sum of all payments in a given category for a specified date range?

For example, suppose you want to determine a total for the following:

Category: groceries
From: 01/01/15
To: 06/30/15

Thanks
Avatar of John
John
Flag of Canada image

Give each category a number (like an account) and then use the SUMIF function to add by account. I do this a lot for similar accounting sheets.
Avatar of Subodh Tiwari (Neeraj)
See if a setup something like this works for you.
Conditinoal-Sum.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Here's a quick demo of using it to create a P&L. It uses the categories defined in the P & L and a Table of finance data as the source
Income-Statement.xlsx
Avatar of Excel amusant
Excel amusant

I would do it this way, please see attached example.

=SUMPRODUCT($F$3:$F$8,--ISNUMBER(MATCH($C$3:$C$8,{2016},0)),--ISNUMBER(MATCH($B$3:$B$8,{1,2,3,4,5,6},0)),--ISNUMBER(MATCH($E$3:$E$8,{"Groceries"},0)))

Open in new window


or with formula sumifs, please see attached example.

=SUMIFS($F$3:$F$8,$B$3:$B$8,"<=6",$C$3:$C$8,2016,$E$3:$E$8,"Groceries")

Open in new window

Sumproduct.xlsx
If you don't like the SUMIF, you should investigate the pivot table.  If you are using date ranges like month, quarter, or year (similar to what an Income Statement shows), the grouping of dates column is a great way to do your reporting.  Example to follow..
SUMIF is a HUGE amount less complicated than a Pivot Table (for me) and in the example given, will do the job. I use this myself.
Pivot Table Example...
EE-2.xlsx
Good thought, John.  The pivot table requires a little learning.  The advantage of the pivot table is the ability to see the same data multiple ways which often is important when looking at financial data.
I've used a PivotTable for this with PowerPivot. However, tje example that I posted using SUMIF is from an accounting workbook that I have used for years.I use SUMIFS to to produce a report by months.
Avatar of WeThotUWasAToad

ASKER

Thanks.
You accepted exactly the same solution (Use SUMIF) as I gave you earlier.
John, you didn't give an example of the formula and how to apply it to a P & L report. I was once told here that a good answer should give examples, etc.

In fact your answer was a couple of minutes before mine, but mine could have nbeen sooner if I had not given an example.

Anyway, the OP got the answe he required.
Lord, all you have to do is click on a Formula to see how it works.