The problem of linking books to each other

%3CLINGO-SUB%20id%3D%22lingo-sub-1362785%22%20slang%3D%22en-US%22%3EThe%20problem%20of%20linking%20books%20to%20each%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1362785%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EA%20problem%20arose%20in%20linking%20books%20to%20a%20formula.%20When%20the%20linked%20book%20is%20closed%2C%20the%20%E2%80%9CVALUE!%E2%80%9D%20Error%20appears%20when%20starting%20the%20linked%20book.%20data%20is%20updated%20and%20tightened%20What%20could%20be%20the%20problem%3F%20Formula%20apply%20in%20this%20situation%20COUNTIFS%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1362785%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1363159%22%20slang%3D%22en-US%22%3ERe%3A%20The%20problem%20of%20linking%20books%20to%20each%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1363159%22%20slang%3D%22en-US%22%3EThe%20COUNTIFS%20function%20only%20works%20with%20an%20external%20reference%20if%20the%20referenced%20file%20is%20also%20open%20in%20Excel.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1363313%22%20slang%3D%22en-US%22%3ERe%3A%20The%20problem%20of%20linking%20books%20to%20each%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1363313%22%20slang%3D%22en-US%22%3ECan%20you%20tell%20me%20then%20what%20function%20I%20can%20replace%20COUNTIFS%20so%20as%20not%20to%20constantly%20open%20the%20second%20book%3F%20maybe%20SUMIFS%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1363634%22%20slang%3D%22en-US%22%3ERe%3A%20The%20problem%20of%20linking%20books%20to%20each%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1363634%22%20slang%3D%22en-US%22%3EYou%20could%20pull%20in%20the%20data%20using%20the%20Get%20Data%20button%20on%20the%20Data%20tab%20and%20then%20create%20a%20pivottable%20rather%20than%20using%20the%20COUNTIFS%20or%20SUMIFS%20functions.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

A problem arose in linking books to a formula. When the linked book is closed, the “VALUE!” Error appears when starting the linked book. data is updated and tightened What could be the problem? Formula apply in this situation COUNTIFS

6 Replies
Highlighted
The COUNTIFS function only works with an external reference if the referenced file is also open in Excel.
Highlighted
Can you tell me then what function I can replace COUNTIFS so as not to constantly open the second book? maybe SUMIFS?
Highlighted
You could pull in the data using the Get Data button on the Data tab and then create a pivottable rather than using the COUNTIFS or SUMIFS functions.
Highlighted
I will not be able to transfer all the data through the pivot table, since the search is about the coincidence of names from a chaotic list, the lists are spread over several columns. Or did I misunderstand your thought?
Highlighted
Alternatively, retrieve the data into a separate worksheet of your current file (using the Get Data technique) and then do the SUMIFS or COUNTIFS against that worksheet.
Highlighted
thanks for the recommendation, I will try