Forum Discussion

DazExcel's avatar
DazExcel
Copper Contributor
Oct 07, 2023

How to dynamically total/count across multiple columns of a dynamic array

Hi,   I'm trying to create a dynamic column that will give me a count based on two columns of a dynamic array.   So if I've got three products across four offices like this... I want to sh...
BI & Data Analysis
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 07, 2023

DazExcel 

For such sample

that could be

=LET(
    office, CHOOSECOLS(data, 1),
    product,CHOOSECOLS(data, 2),
    sales,  CHOOSECOLS(data, 3),
    k,      SEQUENCE(, ROWS(office), , 0),
    uOffice,UNIQUE(office),
    uOP,     SORT(UNIQUE(CHOOSECOLS(data, {1,2}))),
    mm, --(office & product = TRANSPOSE(CHOOSECOLS(uOP, 1) & CHOOSECOLS(uOP, 2))),
    count, TRANSPOSE(MMULT(k, mm)),
    total, TRANSPOSE(MMULT(k, mm * sales)),
    VSTACK(
        {"Office","Product","Count","Total"},
        HSTACK(uOP, count, total)
    )
)

 

Resources