SPILL error when SUMIFS on pivot table

[ryansm05]

Hi,

I was using a SUMIFS formula to look up an external workbook which worked great.

I then decided to pivot table that external workbook so that the data was contained within my file, and users of the file did not need to open that external workbook each time.

However, since doing this, I'm struggling to rebuild the file when recreating the SUMIFS formulas via the pivot table. For example, I create my SUMIFS formula in cell B5 which works great. I then add a new unique formula into cell B6 which also works great, but simultaneously the formula in B5 returns #SPILL!. The issue seems to be because I'm looking-up from the pivot table?

Is there a way to bypass this? Any help would be much appreciated.

Thanks
Ryan

 

[ryansm05]

Please ignore - user error as one of my lookups was merged across two cells.