COUNTIFS and COUNTIF in a Sharepoint spreadsheet returning #VALUE!

[jason7579]

I am having issues with returning results for =COUNTIFS and =COUNTIF when referencing a dataset in a different Sharepoint spreadsheet (both hosted in the same Sharepoint site).

I have got an INDEX and MATCH working fine, so I am pretty sure it is not an issue with the spreadsheet connection or permissions.
I have got the COUNTIF to work within its own spreadsheet (on a different tab) so my formula appears to be correct.

The formula I have is

=COUNTIFS('Sharepoint https address[Spreadsheet name.xlsx]Tab name'!$A:$A,"ABC",'Sharepoint https address[Spreadsheet name.xlsx]Tab name'!$H:$H,1,'Sharepoint https address[Spreadsheet name.xlsx]Tab name'!$U:$U,[@Date])

I am trying to count the number of cells in [other spreadsheet] column A that is named ABC;
that has the number 1 in [other spreadsheet] column H;
where the date (in other spreadsheet column U) matches the date in the formula row on my current (active) spreadsheet.

I have tried to find the error by breaking it down and doing each calculation as a COUNTIF but am still getting #VALUE!
I have made sure that the string is not over 255 characters.

Any ideas? Thanks

 

[jason7579]

Updated: I have now found that I can open the spreadsheets to edit in the desktop app and all appears fine, so the links work fine when in app but not when contained in Sharepoint, although the INDEX MATCH in my other spreadsheet works fine when not in app? I am more confused by this.