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