Forum Discussion
Solved
If, Countifs Formula Dynamics Table Array Error
We have established an internal tracking system. This system relies upon a single workbook with multiple worksheets, but has formulas that are very interwoven and interdependent. There are over 20 tables in the workbook (approximately 12 MB).
I have created a if, countifs formula in a table for if two criteria apply, then provide the value in a column in a table. It is structed like =if(countifs(TableB[Name],[@[office.y]],TableB[WOAR],1), TableB[Local Administrator],""). Unfortunately, when the argument is true, at its first encounter it generates a #SPILL error. Through research, I have discovered the issue here is it is a dynamics array error because it cannot SPILL in a table. I have very similar formulas which work but the true value is a static, exact value (e.g., "Y", or "Y, WOAR"). I have tried all manner of different approaches, but it appears the failure is because the return/true value is a dynamic value from within a table.
Can someone inform me if I am approaching this wrong? Is there another way to accomplish the same thing? If a row in a table meets various criteria, I want it to return the value in a specified column in that row.
Got it. I put together a sample workbook with a similar arrangement.
=XLOOKUP([@Facility]&1,TableB[Facility]&TableB[WOAR],TableB[Local Administrator],"")
13 Replies
The bolded is the part the table doesn't like:
=if(COUNTIFS(TableB[Name],[@[office.y]],TableB[WOAR],1), TableB[Local Administrator],"")
Perhaps this:
=if(COUNTIFS(TableB[Name],[@[office.y]],TableB[WOAR],1), [@[Local Administrator]],"")- Thank you for the swift reply! To be sure, the [@[office.y]] is a column in the table of the formula under review. The TableB[LocalAdministrator] portion is in a different table on a different tab of the same workbook. It appeared using the @ was only something automatically assigned from within the same table. If I just use the @ it will know it is from a different table in a different sheet?
I see, what are you looking to return from TableB[LocalAdministrator] ? We'd have to pick a single value out of the array to eliminate the SPILL error.
