Counting non-empty merged cells on another closed document with SUMPRODUCT

[Pisus]

Hello.
I am trying to count non-empty cells on another closed document with SUMPRODUCT.
I am using this formula:

=(SUMPRODUCT(('https://link/[Workbook.name]Worksheetname'!$Q$9:$Q$99999>"")+0))

Everything seems fine until the array encounters merged cells and returns #REF! from those cells as seen in formula evaluation window:

How to solve this problem. Easiest way is just to unmerge those cells or there are other formulas? Maybe there is a way to change those #REF! to 1's? Or maybe a VBA macro would be more suitable?
Thank you for your insights and help.

P.S. same question posted here

 

[fadee2]

for

Maybe there is a way to change those #REF! to 1's??

try

=iferror(SUMPRODUCT(('https://link/[Workbook.name]Worksheetname'!$Q$9:$Q$99999>"")+0), 1)

that should help u out.

 

[fadee2]

hi pisus,
Did the answer worked for you?
do let us know?