COUNTIF formula problems

[artisanpp]

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

[Peo Sjoblom]

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

[artisanpp]

Hi Peo

Thank yor for your help, I will try this.

best regards
gino