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

Answer 2

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}

Answer 3

Approach via FilterXML()

For versions supporting LET (MS 365) let's develop a basic formula logic based on ►FilterXML() (assuming e.g. A1# as spill range reference):

    =LET(xml,"<all><i>"&TEXTJOIN("</i><i>",TRUE,A1#)&"</i></all>",xpath,"//i[.='pets']",COUNTA(FILTERXML(xml,Xpath)))

where

• xml creates a wellformed xml string (roughly comparable to a hmtl tag hierarchy)
• xpath defines the search expression string matching the value of each <i> node against 'pets' via "//i[.='pets']"
• COUNTA(FILTERXML(xml,Xpath)) counts the found elements of filterXML()

Caveats:

a) Hypothetical caveat

The formula above assumes that no individual name equals the generic name pets, i.e.g that there isn't any specific animal breed that is named "pets" as individual identifier.

If you want to exclude this improbable case, too just enter the following formula where the xpath expression refers to the neighbour column elements:

    =LET(xml,"<all><i>"&TEXTJOIN("</i><i>",TRUE,A1#)&"</i></all>",Xpath,"//i[.='pets' and position() mod 2 = 0]",COUNTA(FILTERXML(xml,Xpath)))

b) Integrate non-findings

Non-findings result in a #Value! error, which COUNTA, however would accept as 1 found element. On the other hand, enclosing FilterXML in an IF(IsError(FilterXML(..)),0,..) condition would spill the whole result if there are findings n times, which can be avoided by integrating an error check like IF(err,0,COUNTA(FILTERXML(xml,Xpath):

Refined formula

=LET(spill,A1#,srch,"pets",xml,"<all><i>"&TEXTJOIN("</i><i>",TRUE,spill)&"</i></all>",Xpath,"//i[.='" & srch & "' and position() mod 2 = 0]",err,IsError(MATCH(srch,INDEX(spill,,2),0)),IF(err,0,COUNTA(FILTERXML(xml,Xpath))))

In order to make the formula more readable, I display it with line breaks:

=LET(
    spill, A1#,
    srch,  "pets",
    xml,   "<all><i>"&TEXTJOIN("</i><i>",TRUE,spill)&"</i></all>",
    Xpath, "//i[.='" & srch & "' and position() mod 2 =0]",
    err,   ISERROR(MATCH(srch,INDEX(spill,,2),0)),
    IF(err, 0, COUNTA(FILTERXML(xml,Xpath)))
)

Recommended links

• @JvdV 's nearly encyclopaedic post at SO Extract substrings from string using FilterXML

• W3.org XML Path Language (XPath 1.0) and XPath Cover page

Alternative //edit as of 2021-11-16

Just for the sake of the art another approach translating all matches into the numeric value 1, which allows to sum them up. Note the unconventional double array argument src in the second MATCH(src,src,0):

=LET(src,INDEX(A1#,,2),srch,"pets",row,MATCH(srch,src,0),err,ISERROR(row),IF(err,0,SUM(IF(MATCH(src,src,0)=row,1,0))))