Countif using 3-D References - need help

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

Have a workbook with multiple spreadsheets. With the
exception of the first spreadsheet all are identical.
Each Cell will contain either, "y", "n" or "p". Need to
total up the number of "y"s, "n"s and "p" from each cell
on each sheet on the master spreadsheet.

countif was the perfect function except it does not
support 3-D references,
i.e. countif(Sheet2:sheet13!B4,"y") does not work

The total number of spreadsheet is unknown which also
made the 3-D References attractive, since as long as the
first and last sheets are defined anything added in the
middle is automaticly accounted for.

Anyone know of a better way to do this.

Thanks

Rich
 
-----Original Message-----
Have a workbook with multiple spreadsheets. With the
exception of the first spreadsheet all are identical.
Each Cell will contain either, "y", "n" or "p". Need to
total up the number of "y"s, "n"s and "p" from each cell
on each sheet on the master spreadsheet.

countif was the perfect function except it does not
support 3-D references,
i.e. countif(Sheet2:sheet13!B4,"y") does not work

The total number of spreadsheet is unknown which also
made the 3-D References attractive, since as long as the
first and last sheets are defined anything added in the
middle is automaticly accounted for.

Anyone know of a better way to do this.

Thanks

Rich
Rich,

Have same problem, both within a single worksheet and
among several worksheets. Found workaround on line using
an array, SUM(IF((RANGE=CRITERIA)+(RANGE=CRITERIA),1,0)),
but still cannot get it to provide accurate count if data
in first column and data in second column meet criteria.
In our previous version of Excel, I was able to use an
array with COUNTIF and it worked well.

Does anyone have a solution?

Thanks,

Barbara
 
Try this one Rich

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet" &(ROW(INDIRECT("2:13")))&"!B4"),"y"))
 
Thanks,

That does fill the major part of the requirement. Does
not allow me to add additional sheets at will and
dynamically recalculate as the 3-D reference would -- but
it gets the job done -- and that is the important part.

An additional question if I may -- suppose I wanted to
make each worksheet a different name, for example instead
of sheet2 thru sheet13 they are called joe, paul, pete,
etc. Is there a way the formula could be changed to
support that??

Again thanks

Rich
-----Original Message-----
Try this one Rich

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet" &(ROW(INDIRECT ("2:13")))&"!B4"),"y"))

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




"Rich" <[email protected]> wrote in
message news:[email protected]...
 
Use a range with the sheet names

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A12&"'!B4"),"y"))

where A1:A12 hold the sheet names
 
Back
Top