How to make structured references with dynamic arrays?

Question

This is a followup question from this earlier thread

I have output that comes from a sequence() function. I want to run a calculation that sums up the output from the sequence() function. In particular, I want it to be structured so that my calculation will be right, even if the underlying data gets additional rows. I'll describe this in more detail with example data.

Say my output from the sequence() function looks like this

name      source 
banana    fruits
orange    fruits
peach     fruits
dog       pets
fish      pets
cat       pets

Now let's say I want to create a calculation that summarizes the collated dataset with a simple condition.

If this were a table, I would do something like this- countifs(tblCollated[name], tblCollated[source], "pets")

Anyway, the point is that I cannot do this because I cannot make the output of a sequence() into a table.

Answer 1

Using LET and spill range references you can create larger complex functions that self-reference;

For example, assigning the column of data you want to col = 2 then indexing the second column from the spill range reference $A$2# gives a spill list of just that data you can manipulate

=LET(col,2,list,INDEX($A$2#,,col),list)

You can then further manipulate that list such that the count matching "Pets" is:

=LET(col,2,list,INDEX($A$2#,,col),COUNTIF(list,"Pets"))

Using a more dynamic LET formula, makes it a bit easier to quickly alter your formula if you need to, and also makes it a bit more readable.

Or you could use a filter on that list:

=LET(col,2,list,INDEX($A$2#,,col),ROWS(FILTER(list,list="Pets")))

And allowing for error handling (Thanks @T.M.):

=LET(col,2,list,INDEX($A$2#,,col),IFERROR(ROWS(FILTER(list,list="Pets")),0))