summarizing by year

[ljvb]

I have an income statement table (worksheet IS) with structure as such:
Rows: date by months in row1 (Jan 2015, Feb 2015,...), year of month in row2 (2015, 2015,...).
Cols; A empty, B has the labels of the line items in the income statement (revenue, expenses, interest earned, net income etc.).

I want to summarize the IS table by year in a summary worksheet where row1 is the year field. I thought the formula below would work to pick up the interest earned item totaled by year but it only picks up the first instance of the year match, not all instances of the year match:

=SUMIF(IS!$B$2:$B$494,"Interest Earned",INDEX(IS!$C$2:$CC$2,MATCH(E$1,IS!$C$2:$CC$2,0)))

Any help on adding all columns that have that year match for the specified line item? Thank you.

 

[jorismoerings]

Hi,

Assuming the year reference is located in cell E1, give this a try: =SUMPRODUCT((IS!$B$2:$B$494="Interest Earned")*(IS!$C$2:$CC$2=E1)*(IS!$C$3:$CC$494))

 

[ljvb]

Thank you for that. Unfortunately, the final element in the sumproduct (IS!$C$3:$CC$494) exceeds the 8192 character limit for an excel formula. I tried to narrow it down but it made now difference. Thoughts?

 

[jorismoerings]

What’s causing the overrun? Could it be the criteria in the second part of the sumproduct function because the rest definitely didn’t exceed 8192 characters.
Perhaps the “ interest earned” part can be replaced by a cell reference.

 

[ljvb]

I'm not all that familiar with the overrun issue but perhaps it considers all the data in the element, which would take it over the 8192 characters? It's definitely that final element that's getting the message.

 

[jorismoerings]

Are you able to share your document via Box, or Onedrive or ... somehow so i can take a look

 

[ljvb]

Thank you for setting this up correctly in the spreadsheet I shared. Can you share how you removed the overrun issue? I'm trying exactly the same thing for another spreadsheet (statement of cashflows) and it's doing the same thing (see the shared workbook from before).

 

[jorismoerings]

Your welcome. I can’t show you how because I couldn’t reproduce the overrun issue you mentioned. I can take a look at the other sheet as well if you want me to.

 

[ljvb]

I set up a statement of cashflows in the sample sheet and the error is there for cell C66

 

[jorismoerings]

ok, the sumproduct formula is correct but will evaluate the FULL data set and can't handle errors without extra support.
As your data set contains #N/A in 4 cells in column BS, it will evaluate to an error.

So basically you'll have 2 options depending on the which excel version you're using:

1. Correct the errors in your data by removing the #N/A and replace them with 0 is the easiest option;
2. If you want the error handled the solution is dependable on the version of excel:
   1. if you're using the desktop version: just change the data part (SCF!$C$5:$BS$71) to (IFERROR(SCF!$C$5:$BS$71,0)) and make sure you confirm the formula not with Enter but by CTRL+Shift+Enter as the confirmation for entering a ARRAY formula.
   2. if you're using the online version: You need to download the file to the desktop version change the data part as described above and reload it back to Online.

If it was me, i would choose option 1 as it's the most cleanest.