This will work

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Sea King Adv. june1";"Myrtle Winchester
june2"}&"'!A22:A45"),"ZY340"))


better to use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&N1:N2&"'!A22:A45" ),"ZY340"))


Where N1 and N2 holds the 2 names of the sheets, if you expand ywith let's
say 2 more sheet you can just change it to

=SUMPRODUCT(COUNTIF(INDIRECT("'"&N1:N4&"'!A22:A45" ),"ZY340"))

and put the sheet names into the cells


Regards,

Peo Sjoblom

"artisanpp" wrote:


Hi All

I am trying to use this countif formula for all the sheets in my
workbook (currently only 2 sheets)
=COUNTIF('Sea King Adv. june 1:Myrtle Winchester
june2'!A22:A45,"ZY340")

sea king and myrtle represent the name of the sheets, and I am trying
to see if the text ZY340 appears in cells a22 to a45 of both sheets,
and if it does to total it on another blank sheet which I will call
sheet 1

Using the formula for a single sheet does work, but when I add the
other sheet I keep getting an error

If anyone could help me refine this and make it work that would be
excellent

thanks in advance
gino


--
artisanpp
------------------------------------------------------------------------
artisanpp's Profile: http://www.excelforum.com/member.php...o&userid=23593
View this thread: http://www.excelforum.com/showthread...hreadid=375801