Stack columns with dynamic lookups across your spreadsheet tabs

Here's a problem that feels familiar to anyone who has tried to push a spreadsheet beyond its original design. A user wants to stack two columns: one is a straightforward reference across tabs, the other needs to count matching values from multiple sheets and return those counts row by row. On paper, it sounds like a simple HSTACK operation. In practice, the formula works for the first row and then collapses.

The core issue is that COUNTIF, when given a range for the criteria argument, returns a single aggregate value rather than an array of per-row results. The user's instinct to wrap it in SUM is understandable, but it flattens the logic into one number. What they need is a formula that evaluates each row independently, not a blanket total. This is where the limitations of traditional spreadsheet functions become visible. The user has already done the hard work: they set up a named range for dynamic sheet references, they understand INDIRECT, and they know what the output should look like. The tool itself is what falls short.

For anyone building more complex workflows, this moment is a signal. The workaround exists, but it requires nesting MAP or BYROW to force per-row evaluation, or restructuring the data into a single normalized table before attempting cross-tab lookups. Neither option is intuitive, and both demand a level of formula fluency that most users shouldn't need just to stack two columns. The real takeaway is that when your spreadsheet logic starts fighting you on something this basic, it's worth asking whether the tool is meeting you halfway or making you bend to its limitations.

The user's solution is close, they just need to replace the SUM(COUNTIF(...)) with a MAP that iterates each row of x through a COUNTIF wrapped in SUM. That change turns a broken formula into a working one. But the lesson here isn't just about correct syntax. It's about recognizing when a spreadsheet is asking you to compensate for its design rather than enabling yours. The best tools don't make you fight for a simple column of results. They let you ask the question and get the answer, row by row, without the workaround.