COUNTIF formula problems

  • Thread starter Thread starter artisanpp
  • Start date Start date
A

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