Indirect sumproduct.

[saint_2008]

Hi Hopefully someone can help I'm trying to use an indirect to a sheet name using Cell B5 then I want to look up how many cells are not blank in "A" using a sumproduct function.

I've tried using the following when I go though step by step looks like its going to work but I end up with a #Ref error from the indirect.

'SUMPRODUCT(--(INDIRECT("'"&B5&"'!A:A<""")))

Many Thanks

 

[steve the fish]

Try like this:

=SUMPRODUCT(--(INDIRECT("'"&B5&"'!A:A")<>""))

or as full column references arent ideal with SUMPRODUCT you could try:

=COUNTIF(INDIRECT("'"&B5&"'!A:A"),"<>")

 

[saint_2008]

Cheers bud

I see where I went wrong, just needed to separate the last part.