Formula to replace CountIf with a ">0" criteria

[kurtkaufman]

Right now I have a Summary workbook (let's call it [Summary.xlsx]) which has links to about 60 other excel documents (I'll refer to as [Other.xlsx]). I'm trying to pull the last number in a column from the [Other.xlsx] documents into a cell on the summary workbook, but the [Other.xlsx] is updated weekly so I want the last number to be automatically updated into [Summary.xlsx].

I originally attempted using:

=INDEX('[Other.xlsx]Template'!$L$41:$L$45,COUNTIF('[Other.xlsx]Template'!$L$41:$L$45,">0"))

This worked exactly how I intended, but after closing [Summary.xlsx], I would get a #VALUE! error upon re-opening the file. After researching why this was happening, I found out that COUNTIF and SUMIF functions do not work well while referencing other closed workbooks. Also, I updated the Trust Center but this did not help.
I tried using a SUM(IF()) function, but to no avail due to the ">0" criteria. Everything I found online was for a "=" criteria.

Does anyone have any suggestions?

 

[Jonmo1]

Welcome to the board.

Try

=INDEX('[Other.xlsx]Template'!$L$41:$L$45,SUMPRODUCT(--('[Other.xlsx]Template'!$L$41:$L$45>0)))

 

[kurtkaufman]

This appears to have solved my problem! You're a magician, and a very timely one at that. Very much appreciated Jonmo1.

 

[Jonmo1]

You're welcome