Hello all

Long time reader, first time poster.

i commonly use the sumproduct method to countif with multiple criteria,

eg =SUMPRODUCT((A1:A5="oranges")*(B1:B5="yes"))

i now need to do this for multiple sheets, and would like to avoid stringing together a series of sumproduct statements for each sheet.

i've recently discovered 3-d referencing but i am struggling to apply it to my formula.

this is my attempt.......where sheetnames contains a list sheetnames to reference......which yields a #VALUE! error

=SUMPRODUCT(((INDIRECT("'"&sheetnames&"'!A1:A5"))="oranges")*((INDIRECT("'"&sheetnames&"'!b1:b5"))="y"))

am i close, or am i going about this completely the wrong way.

any help would be appreciated

Many thanks