Question: This is a followup question from this earlier [thread](https://stackoverflow.com/questions/69858903/query-another-table-in-excel)

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: 
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))

```
For versions of Excel that suppot `LET`

```
=LET(dat,YourFormulaThatReturnsTable,HdrCol,MATCH("source",INDEX(dat,1,),0),COUNTIFS(INDEX(dat,,HdrCol),"pets"))

```

`YourFormulaThatReturnsTable` can be either

* the actual formula that returns the collated data, or
* the cell reference to the cell that contains your spill formula followed by `#`  

Eg if your collating formula is in `D4` then it would be `D4#`

Obviously, you can replace `"source"` and/or `"pets"` with cell references