This post from TypicalFinanceGuy captures the exact moment where a user's competence meets a tool's hidden edge case. He has built a working PIVOTBY with multiple value columns and a filter. He knows the sort order array should work because he has seen it demonstrated. And yet, when he adds curly brackets for a multi-sort, the formula returns a #VALUE! error. He is not missing anything obvious. He is hitting a real limitation in how Excel currently interprets the `sort_order` argument when it is passed as a literal array.
The issue is a mismatch between syntax and function. When you supply a single number for `sort_order`, say, `-1` for descending, Excel understands it as a scalar. But when you use curly brackets like `{-1,-6,-3}`, you are creating a horizontal array constant. PIVOTBY expects this argument to be a row vector, and in many contexts, a horizontal array constant is exactly that. The problem arises because the function's internal logic expects the array to be generated from a range reference or a formula that returns an array, not from a hard-coded constant written directly in the cell. The error is not that multi-sort is impossible; it is that the literal curly-bracket syntax triggers a validation failure in this specific function. The workaround is to replace the constant with a formula that produces the same array, such as `CHOOSE({1,2,3}, -1, -6, -3)` or by referencing a helper range containing those values.
For users building complex financial models or operational dashboards, this distinction matters. You should not abandon PIVOTBY because of this quirk. Instead, treat the `sort_order` argument as you would any other array input in modern Excel: if a literal constant fails, wrap it in a function that returns an array. The formula will then sort your grouped data by the first row field in descending order, then by the second in descending order, then by the third in descending order, all inside the pivot table itself. That is precisely what TypicalFinanceGuy wants, and it works when the array is delivered through a formula rather than a static constant.
This is not a bug. It is a signal that the new dynamic array functions still carry syntax expectations inherited from older worksheet logic. The lesson is practical: when a demo works on screen but fails in your sheet, check whether the input is a constant or a computed array. Replace the brackets with `CHOOSE` or a `LAMBDA` helper, and your multi-sort will run. That is the last piece of the puzzle, and it does not require a workaround outside the formula.