Sunday, December 25, 2011

yogi_Sum Up Cells In Different Sheets of A Spreadsheet Using Only A Single Formula

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
user puyan said
So if I have =SUM('Dec 2011'!D42;'Jan 2012'!D42 its the sum of December and January's profit. My problem is that if I have a client's sheet and it only contains a December sheet only, it comes up with an error because January sheet is not there. Is there a way of making the formula work (or a more efficient way) so that if the sheets that are not there it can ignore it and move on?
2. is trying to count the number of months that are active. This is also shown by a single cell which counts the days, so if the day's number is over 0 then I want it to be counted. Also, if there if the months are not shown is there a way of ignoring it?
So to clarify, as an example;
Bob's Spreadsheet (4 tabs), Jan 2011, Feb 2011, March 2011, and Summary Tab
Mike's Spreadsheet (5 tabs), Feb 2011, March 2011, April 2011, May 2011, and Summary Tab
In all Monthly Tabs - Monthly Profit, Days Active
In all Summary Tabs (I want) - Overall Monthly Profit, Months Active 
following is a solution to the problem