Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    multi-sheet 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.

    Where-as =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.

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multi-sheet 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

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: multi-sheet 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

  4. #4
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multi-sheet 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

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: multi-sheet 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

  6. #6
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multi-sheet formula (2000)

    After entering the formula into A15 it gives me a result of 1 which is not correct.

    See attached

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: multi-sheet 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" (1-80?) 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 1-80 sheets or even create a summary sheet that would "extract" all the info to format the sheet a particular way.

    Steve

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: multi-sheet formula (2000)

    Formula you used counts zeros:

    =SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT("1:3"))& "!A1"),"0"))

    Formula I provided counts greater-than-zeros, 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

  9. #9
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multi-sheet formula (2000)

    John;

    Thanks for the clarification. The formula works as needed. Thanks for the little lesson.

    This board is fantastic.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •