multi-sheet formula (2000)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

=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 Aladin Akyurek for this method in post 277806.

Reply | Quote

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

Reply | Quote

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″))

Reply | Quote

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

See attached

Reply | Quote

Formula you used counts zeros:

=SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT(“1:3″))&”!A1″),”0″))

Formula I provided counts greater-than-zeros, note the > sign:

=SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT(“1:3″))&”!A1″),”>0″))

See attached.

Reply | Quote

John;

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

This board is fantastic.

Reply | Quote

John;

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

This board is fantastic.

Reply | Quote

Formula you used counts zeros:

=SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT(“1:3″))&”!A1″),”0″))

Formula I provided counts greater-than-zeros, note the > sign:

=SUMPRODUCT(COUNTIF(INDIRECT(ROW(INDIRECT(“1:3″))&”!A1″),”>0″))

See attached.

Reply | Quote

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

See attached

Reply | Quote

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″))

Reply | Quote

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

Reply | Quote

=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 Aladin Akyurek for this method in post 277806.

Reply | Quote

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

Reply | Quote

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

Reply | Quote