Search code examples
excelexcel-formuladynamic-arrays

How can I sum part of a multidimensional dynamic array in Excel M365 with criteria for specific values?

Wondering if it's possible in Excel M365 to sum part of a multidimensional dynamic array, with criteria using a non-looping solution. I am looking to preserve rows of the 'DataTable' dynamic array, and only sum columns for category A and B.

Thanks in advance!

Input:

enter image description here

Desired solution:

enter image description here

I've done this in VBA using a looping solution, but would prefer to build logic into Excel dynamic arrays.

Solution

  • The following approach works for an arbitrary number of columns and letters, that could be repeated. It spills the entire result, no need to drag the formula down:

    =LET(ltrs,B1:D1, in,B4:D6, ux,UNIQUE(ltrs,1),
 out, DROP(REDUCE("",ux,LAMBDA(ac,a, HSTACK(ac,
 MMULT(in,TOCOL(N(ltrs=a)))))),,1),VSTACK(ux,out))

    It uses REDUCE/HSTACK pattern(1). It iterates over each unique letters (ux) and concatenates the result of MMULT horizontally via HSTACK, which calculates the sum of the columns that corresponds to a given letter (a).

    Here is the output: output

    Other alternatives (more verbose):

    Using MAKEARRAY: Usually MAKEARRAY is more efficient than the combination of REDUCE/HSTACK, but I don't think for this case, since the second input argument of FILTER is the same for every row of the same column. I put it here for illustrative purposes, since the first formula is more concise and probably more efficient for a large dataset with a high frequency of repeated letters.

    =LET(ltrs,B1:D1, in,B4:D6, ux,UNIQUE(ltrs,1), seq,SEQUENCE(,COLUMNS(ltrs)),
 out, MAKEARRAY(ROWS(in),COLUMNS(ux),
  LAMBDA(r,c,SUM(INDEX(in,r,FILTER(seq, 1-ISNA(XMATCH(ltrs,INDEX(ltrs,c)))))))),
 VSTACK(ux,out))

    Using BYROW, CHOOSECOLS and REDUCE/HSTACK pattern(1): It could be more efficient than the first approach, since the sum only considers a subset of the columns that match the letter. The first approach does the multiplication for the entire matrix on each iteration.

    =LET(ltrs,B1:D1, in,B4:D6, ux,UNIQUE(ltrs,1), seq,SEQUENCE(,COLUMNS(ltrs)),
 out, DROP(REDUCE("",ux,LAMBDA(ac,a, HSTACK(ac,
  BYROW(CHOOSECOLS(in,FILTER(seq,1-ISNA(XMATCH(ltrs,a)))),
   LAMBDA(x,SUM(x)))))),,1),VSTACK(ux, out))

    Here some performance results that confirm what we suspect. For 1000 rows and 100 columns:

    MMULT MAKEARRAY BYROW
    90ms 890ms 50ms

    Testing for larger number of rows the BYROW solution trumps over the other two. If the letters are highly repeated, i.e. the output has a fewer number of columns, then MMULT performs slightly better.

    (1): Check my answer to the following question: how to transform a table in Excel from vertical to horizontal but with different length