Results 1 to 9 of 9
Thread: multisheet formula (2000)

20040318, 20:19 #1
 Join Date
 Feb 2004
 Location
 Buffalo, New York, Wales
 Posts
 40
 Thanks
 0
 Thanked 0 Times in 0 Posts
multisheet formula (2000)
Hey gang;
I have created a spreadsheet which uses 80 different worksheets for numerous monthly entries. The sheets are called 1,2,3 ....79,80. In cell I3 of each sheet, an amount can be entered although there are some instances where it stays as 0.00. I have been trying to come up with a formula that looks at the multiple sheets and counts how many of the 80 I3 cells have an amount greater than 0.00.
Whereas =SUM('1:80'!I3) will give me the total of all the cells, I can't seem to find a solution to count how many entries are more than 0.00. I thought countif would do the trick but I only get errors.
Thanks for any input.

20040318, 20:33 #2
 Join Date
 Dec 2000
 Location
 California, USA
 Posts
 1,758
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: multisheet formula (2000)
A simplistic approach would be to create a new sheet with cell references to each sheet and sum them or perform a count if. Example A1 = Sheet1!I3, A2 = Sheet2!I3, A3 = Sheet3!I3 and etc.. Sum A1:A80 in cell A81 or use countif.
John

20040318, 21:15 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: multisheet formula (2000)
=SUMIF(), =COUNTIF() and several other useful functions don't work on a 3D multiple sheet basis. To count cells greater than zero, try this:
=SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT("'1:80") )&"'!I3"),">0"))
and this should work on summing all cells greater than 0:
=SUMPRODUCT(SUMIF(INDIRECT(ROW(INDIRECT("'1:80"))& "'!I3"),">0"))
(I only tested these with 4 sheets.) Credit to <!profile=Aladin Akyurek>Aladin Akyurek<!/profile> for this method in <post#=277806>post 277806</post#>.John ... I float in liquid gardens
UTC 7ąDS

20040318, 23:27 #4
 Join Date
 Feb 2004
 Location
 Buffalo, New York, Wales
 Posts
 40
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: multisheet formula (2000)
John;
Attached is an example file which hopefully shows what I am looking for, although I shortened it to three sheets. On the RECAP sheet are comments on what I am looking to do. Maybe you will have a solution.
Thanks

20040318, 23:39 #5
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: multisheet formula (2000)
Sorry, in testing I didn't get the exact syntax. In your example workbook in cell A5 use this formula:
=SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT("1:3"))& "!A1"),">0"))John ... I float in liquid gardens
UTC 7ąDS

20040318, 23:48 #6
 Join Date
 Feb 2004
 Location
 Buffalo, New York, Wales
 Posts
 40
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: multisheet formula (2000)
After entering the formula into A15 it gives me a result of 1 which is not correct.
See attached

20040319, 02:08 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: multisheet formula (2000)
If sheets 1  80 are identically formatted, I find a much better way is to combine them into 1 sheet. If need be add an extra column with some "sheet reference" (180?) It is much easier to maintain and you have built in routines to handle summarizing (FIltering, subtotals, dFunctions, pivot tables), etc.
You could use datafilter to filter the data to essentially look like each of your current 180 sheets or even create a summary sheet that would "extract" all the info to format the sheet a particular way.
Steve

20040319, 02:33 #8
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: multisheet formula (2000)
Formula you used counts zeros:
=SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT("1:3"))& "!A1"),"0"))
Formula I provided counts greaterthanzeros, note the <font color=red>></font color=red> sign:
=SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT("1:3"))& "!A1"),"<font color=red>></font color=red>0"))
See attached.John ... I float in liquid gardens
UTC 7ąDS

20040319, 16:05 #9
 Join Date
 Feb 2004
 Location
 Buffalo, New York, Wales
 Posts
 40
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: multisheet formula (2000)
John;
Thanks for the clarification. The formula works as needed. Thanks for the little lesson.
This board is fantastic.