Excel 2007 : Averaging no blanks and no zeros over several Sheets

[kithing]

Hello I am looking for some help with averaging cells over 20 plus sheets. I do not want blanks or zeros counted. This is what I have used with a few variants but I do not get anywhere with it.

=SUM('WK1 Mon'!D13:'WK1 Tue'!D13:'WK1 Wed'!D13:'WK1 Thu'!D13:'WK1 Fri'!D13:'WK2 Mon'!D13:'WK2 Tue'!D13:'WK2 Wed'!D13:'WK2 Thu'!D13:'WK2 Fri'!D13:'WK3 Mon'!D13:'WK3 Tue'!D13:'WK3 Wed'!D13:'WK3 Thu'!D13:'WK3 Fri'!D13:'WK4 Mon'!D13:'WK4 Tue'!D13:'WK4 Wed'!D13:'WK4 Thu'!D13:'WK4 Fri'!D13:'WK5 Mon '!D13:'WK5 Tue'!D13:'WK5 Wed'!D13:'WK5 Thu'!D13:' WK5 Fri'!D13)/sumproduct/(countif(indirect(""&'WK1 Mon'!D13:'WK1 Tue'!D13:'WK1 Wed'!D13:'WK1 Thu'!D13:'WK1 Fri'!D13:'WK2 Mon'!D13:'WK2 Tue'!D13:'WK2 Wed'!D13:'WK2 Thu'!D13:'WK2 Fri'!D13:'WK3 Mon'!D13:'WK3 Tue'!D13:'WK3 Wed'!D13:'WK3 Thu'!D13:'WK3 Fri'!D13:'WK4 Mon'!D13:'WK4 Tue'!D13:'WK4 Wed'!D13:'WK4 Thu'!D13:'WK4 Fri'!D13:'WK5 Mon '!D13:'WK5 Tue'!D13:'WK5 Wed'!D13:'WK5 Thu'!D13:' WK5 Fri'!F:N,"<>0")

I have also used =sum(sheet cell)/countif(sheet cells, "<>"0)

any ideas please

[NBVC]

Try this:

List all the names of the sheets in a single column in your summary sheet. Then select that range of sheetnames and go to Insert|Name|Define and call that range: TabNames

Then use this formula:

Please Login or Register  to view this content.

[kithing]

Try this:

List all the names of the sheets in a single column in your summary sheet. Then select that range of sheetnames and go to Insert|Name|Define and call that range: TabNames

Then use this formula:

Please Login or Register  to view this content.

This is what I enterentered and now I get the following #REF!

=SUMPRODUCT(SUMIF(INDIRECT("'"&'Summary '!AA1:AA25&"'!D13"),">0",INDIRECT("'"&'Summary '!AA1:AA25&"'!D13")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&'Summary '!AA1:AA25&"'!D13"),">0"))

Also I should have mention that the Tab names change monthly, but for now that can wait

Thanks

[NBVC]

Are you sure that 'Summary '!AA1:AA25 contains the exact names matching the sheetnames? And that they all exist?

[kithing]

I did not understand that I needed to create a table. I did that on the summary tab. I inserted your Formula exactly as you entered it. My table name is TabNames but now I get the following #NAME?

[kithing]

The names of the tabs are correct in the table as thet are on the Tabs

[NBVC]

You could do it by referencing the range as you had attempted.. it is just easier to manage as a named range.

If you are getting that error, something is typed incorrectly or you did not actually create the name correctly.

Can you post your new formula. ... also go to Insert|Name|Define and check that you have indeed a TabNames name in the list.

If you can post a sample workbook, that would be best way to resolve this.

[kithing]

You could do it by referencing the range as you had attempted.. it is just easier to manage as a named range.

If you are getting that error, something is typed incorrectly or you did not actually create the name correctly.

Can you post your new formula. ... also go to Insert|Name|Define and check that you have indeed a TabNames name in the list.

If you can post a sample workbook, that would be best way to resolve this.

I have up loaded the file tahnks for you help

[NBVC]

See attached.

First, you did not have a named range called TabNames created... now I created one. I also made it dynamic, so that you can add to the bottom of the list in column S if you add new sheets.

Also, 2 of the sheetnames were not exactly correct.

The WK5 Mon tab had a trailing space and the WK5 Fri had a space at the front.

[kithing]

Thank you very Much