SUMIFS - #VALUE error with linked workbooks on Onedrive

Hi all

I am hosting some Excel documents on Onedrive. One workbook includes a number of SUMIFS formulas referenced to the second workbook.

I am getting a #VALUE error on all of these formulas, which I understand is a common issue when the formula references a closed workbook and it normally clears as soon as the linked workbook is opened.

Unfortunately I can't seem to clear the error when using Onedrive. Does anybody know of a workaround? I'm reluctant to use array formulas due to the size of files and number of links within them.

Many thanks

Tom