# Excel formula to create typical Profit &amp; 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:

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
John

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
Roy Cox

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

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
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.