Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

3d lookup function across multiple sheets

Posted by Anthony Johnston on January 16, 2002 6:43 PM

I am trying to count up the number of times a specific cell has a value of 'Y' across say 30 sheets in a single workbook and display this on a specific cell on say the first sheet in that workbook.

eg: sheet1 at position B5 I want to insert a formula to count up the number of times a 'Y' appears on position C7 on sheets 2-50. a 3d formula should do it but I must have the syntax wrong cause it just doesn't want to work!...
any input appreciated...

Check out our Excel Resources

Re: 3d lookup function across multiple sheets

Posted by Mark W. on January 17, 2002 7:52 AM

I trust that you understand that you can't use
3-D references in array formulas and that COUNTIF
cannot be used with 3-D references. If the cells
in question are either blank or contain only "Y"
then use the =COUNTA(Sheet2:Sheet50!C7). If the
cells contain a "Y" or "N" then you'll have to
rethink your worksheet design. You could change
the Y's to 1 and the N's to 0, format the cells
as [=1]"Y";[=0]"N" and use =SUM(Sheet2:Sheet50!C7).

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.