1. 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. 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. 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#>.

4. 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. 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"))

6. 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. 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. 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.

9. 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
•