AverageIF across sheet range not working

Using Excel 2003

I need a formlula that averages a cell from 31 sheets, but only averages a zero in IF cell A7 from all 31 sheets does NOT contain the letter X.

I have a monthly (sheet 32) that is pulling numbers from sheets 1-31 for a total picture of the month. One of these cells is an average of cell V3 for all 31 sheets. On some of the 31 sheets I am using an X in A7 to discount anything on that sheet (X signifies a day off). How would I write this formula. We DO want to include the 0 in the average if it was a working day, ie A7 does not contain an X.

I have tried a number of formulas but to no avail. Thanks for the assistance on this.

Hi,

Please take a moment to read the forum rules and adjust your thread title accordingly.

Dom

Thanks.

If you list your sheet names in A2:A32 you can then use:

=AVERAGE(IF(N(INDIRECT("'"&A2:A32&"'!A7"))<>"X",N(INDIRECT("'"&A2:A32&"'!V3"))))

Dom

You have to actually list the sheet names in A2:A32 (or somewhere else you'd prefer). The formula won't accept a 3D reference like that.

Dom

DaddyLonglegs has pointed out that the formula should probably use T rather than the first N with the criteria being text although they both seem to work.

=AVERAGE(IF(T(INDIRECT("'"&A2:A32&"'!A7"))<>"X",N(INDIRECT("'"&A2:A32&"'!V3"))))

@daddylonglegs - I tried to reply to your PM but your inbox is full.

Dom

Nope he's right. Should definitely be T. I also failed to point out that this is an array formula and should be confirmed with Ctrl+Shift+Enter.

Dom

No worries. Please mark the thread solved if you're happy with the solution.

Dom