Forum Discussion

Jwieczerzak's avatar
Jwieczerzak
Copper Contributor
Aug 26, 2022

Text after a Dynamic Array

I would like to put text at the end of a dynamic array formula result but can't seem to find any info. For example, I have a list of values and I would like to put a total at the bottom. Since the list length is dynamic, I don't know how to put the Total in the column without either limiting the length of the list OR causing a SPILL error.

January 100 Electric
January 200 Water
January 100 Sewer
February 110 Electric
February 190 Water
February 104 Sewer
March 150 Electric
March 210 Water
March 144 Sewer

=filter(C1:E9,E1:E9 = "Electric")

Desired Result
January 100 Electric
February 110 Electric
March 150 Electric
TOTAL 360

Since the list is dynamic, how do I ensure that the "TOTAL" is placed at the bottom of the list? Any ideas?

excel
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Aug 27, 2022

    Jwieczerzak If you are user of Microsoft 365 beta channel or current preview then can use VSTACK() and HSTACK(). As per my below screenshot (file is also attached) try-

    =VSTACK(FILTER(C1:D9,E1:E9=H1),HSTACK("Total",SUM(FILTER(D1:D9,E1:E9=H1))))

    If you do not have access to VSTACK() function. Then could try-

    =INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",FALSE,FILTER(C1:D9,E1:E9=H1),"Total",SUM(FILTER(D1:D9,E1:E9=H1)))&"</s></t>","//s"),SEQUENCE(COUNTIF(E1:E9,H1)+1,2))

     

     

  • DexterG_III's avatar
    DexterG_III
    Iron Contributor
    Aug 27, 2022

    Jwieczerzak I find it easier to split this into two formulas, one returning the month per category (+ "Total" row) and the other returning the value per category/month intersection (+Total of all returned values).  

     

    Month per Category

    =LET(
FilteredList, FILTER(C1:C9,E1:E9 = B11),
CountRecords,ROWS(FilteredList),
IF(SEQUENCE(CountRecords+1)>CountRecords,"Total",FilteredList))

     

    Value per Category/Month Intersection

    =LET(
ValueColumn,D1:D9,
CategoryColumn,E1:E9,
MonthColumn,C1:C9,
UniqueList,A13#,
IF(UniqueList="Total",SUMIFS(ValueColumn,CategoryColumn,B11),SUMIFS(ValueColumn,MonthColumn,A13#,CategoryColumn,B11)))

     

     

    Hope this helps,

    Dexter

Resources