WeThotUWasAToad

asked on

# Excel formula to create typical Profit & Loss Statement totals

Hello,

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:

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

Category: groceries

From: 01/01/15

To: 06/30/15

Thanks

**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:

**What 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

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.

See if a setup something like this works for you.

Conditinoal-Sum.xlsx

Conditinoal-Sum.xlsx

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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

Income-Statement.xlsx

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

or with formula sumifs, 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)))
```

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")
```

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

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.

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.

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.