Alternative for Countifs when referencing closed file - multiple lines

[Negligible]

Hi. I've been going through the archives to look for an alternative to using "COUNTIF" as I am referencing a file that is 113 MB and would prefer never to open it again. There are a few suggestions I've seen in searching the archives like trying a combination SUM(IF) or ACOUNT but none work at the level of complexity that I've been using the "COUNTIF" function for. I've posted a sample of the code I've been using.

{=COUNTIFS(
'filelocation\[filename.xlsx]Sheetname'!$C:$C,"A",
'filelocation\[filename.xlsx]Sheetname'!$N:$N,"NO",
'filelocation\[filename.xlsx]Sheetname'!$CL:$CL,"YES",
'filelocation\[filename.xlsx]Sheetname'!$BT:$BT,"YES",
'filelocation\[filename.xlsx]Sheetname'!$F:$F,LEFT(RIGHT($A2,8),3)&" ",
'filelocation\[filename.xlsx]Sheetname'!$G:$G,RIGHT($A2,4))}

Can someone offer some insight into how I might be able to convert this so it will operate if the file is closed?

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

SUMPRODUCT works with closed workbooks.

Maybe something like this (UNTESTED)

=SUMPRODUCT(--('filelocation\[filename.xlsx]Sheetname'!$C$2:$C$1000="A"),
--('filelocation\[filename.xlsx]Sheetname'!$N$2:$N$1000="NO"),
--('filelocation\[filename.xlsx]Sheetname'!$CL$2:$CL$1000="YES"),
--('filelocation\[filename.xlsx]Sheetname'!$BT$2:$BT$1000="YES"),
--('filelocation\[filename.xlsx]Sheetname'!$F$2:$F$1000=LEFT(RIGHT($A2,8),3)&" "),
--('filelocation\[filename.xlsx]Sheetname'!$G$2:$G$1000=RIGHT($A2,4)))


Important: note that the formula uses definite ranges, rows 2 to 1000 (adjust to your real case), not references to entire columns.

For a complete explanation about SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps

M.