Sunday, December 25, 2011

yogi_Operate On Cells In Different Sheets Within A Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
Merry Christmas!
user puyan said:
Countif/Sum cells on different pages
Hi guys, (and merry xmas lol)
I've made a template sheet in which money data is shown monthly with each sheet representing a month.I want to do a summary page in which it takes certain data from each sheet and then sum/counts them.
The 2 main ones I'm trying to do is;
1. Sum total profit from all months.
2. Count/Don't count months where months are active or not.
One of the biggest problems is that for different people they all don't necessarily have all the months added due to the length of their contracts with us.
So that using; (1)
=SUM('Dec 2011'!D42;'Jan 2012'!D42;'Feb 2012'!D42;'March 2012'!D42;'April 2012'!D42;'May 2012'!D42;'June 2012'!D42;'July 2012'!D42;'Aug 2012'!D42;'Sept 2012'!D42;'Oct 2012'!D42;'Nov 2012'!D42;'Dec 2012'!D42;)
is problematic because if the month sheet is not there, it doesn't ignore it but gives out an error message.
and using; (2)
=COUNTIF('Jan 2012'!D44,">0")+COUNTIF('Dec 2011'!D44,">0")+COUNTIF('Feb 2012'!D44,">0")+COUNTIF('March 2012'!D44,">0")+COUNTIF('April 2012'!D44,">0")++COUNTIF('May 2012'!D44,">0")+COUNTIF('June 2012'!D44,">0")+COUNTIF('July 2012'!D44,">0")+COUNTIF('Aug 2012'!D44,">0")+COUNTIF('Sept 2012'!D44,">0")+COUNTIF('Oct 2012'!D44,">0")+COUNTIF('Nov 2012'!D44,">0")+COUNTIF('Dec 2012'!D44,">0")
has the same problem in which if the month sheet is not for that particular client's spreadsheet, it will give out an error message.
Much help appreciated, thx!
following is a solution to the problem