Converting countif into array to work on closed books

[scohn14]

Hi everyone,

I have a formula which worked flawlessly until I tried to use it with the source material closed. I now know that excel returns value errors when a countif references a closed book. The advice I received to work around this is to change the formula from countif(FORMULA) to a version of sum(if(formula=criteria,1,0) except I can't seem to get that to work.

My original formula is posted below, any help is greatly appreciated.

Please Login or Register  to view this content.

Intent is to find if a figure (in this case the text value in L26) is contained in a range on another sheet. If it is in the first range, call it equity, the second range, call it fixed income, and in none, call it non classified.

Thank you!

[snapfade]

Hey scohn14, I seem to recall that the only way you can interact with a closed Excel file is to use a link.

The formula would look a bit different because it references the file a little differerntly. For example,

Please Login or Register  to view this content.

I just tried the above on a closed worksheet and it works for me. You might try your formula using the linking method. Caveat: I have not tried it with a more complicated formula. Suggest in building the formula that you have the other files open and point to those files as part of typing the formula; that causes the linkage.

Hope this helps.

[scohn14]

Hi snapfade,

Thank you for the quick response. Unfortunately this did not correct the errors I am encountering.

My issue seems to stem from the use specifically of countif referencing the closed workbook. It persists even if I link directly to the file with the full path.

As a note, the original pathing I listed in my formula above was created by having both books open and clicking the desired destination to let excel name things accordingly. Also, I have other formulas referencing the same book that work while it is closed.

Thanks in advance to anyone else with any suggestions!

[snapfade]

I can't make it work either, scohn14. I wonder if you could embed the countif in each of the files you are trying to reference. then you would simply refer to the cell of the closed files. Just a thought.

good luck!

[scohn14]

Thank you again for another good idea snapfade. Sadly, the countif is dynamic to each open sheet (one per portfolio), imbedding it in the closed sheet doesn't work either. It creates the same problem in reverse.

[63falcondude]

Try using SUMPRODUCT in place of COUNTIF.

SUMPRODUCT works on closed workbooks.