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
Bookmarks