In a previous post I shared an Excel based income statement (profit and loss) that was based on a data register using sumifs and sum arrays to integrate multiple conditions in both Excel 2007 and earlier versions. However, I originally overlooked another very powerful way to summarize data along multiple conditions that are relatively defined. The worksheets here present the same income statement using a “Getpivotdata” function to pull the information based on relative information.
Once again I had to make two versions (one for Excel 2007 and one for earlier versions) as the pivot table functionality is different and the “iferror” function does not exist in the earlier versions. The only caveat to this worksheet that is a downside from using the others is that you must refresh the pivot table after entering the data before the income statement will update, otherwise this seems to be a very powerful way to summarize the data among a multitude of criteria.
Let me know if you have any thoughts, comments or suggestions on these!