Search code examples
excelexcel-formulamultiple-columns

Consolidating Excel Data With Dynamic Arrays

I'm working in Excel with data that contains multiple columns of criteria and sometimes this criteria has redundant identifiers. In my process, I'm trying to use dynamic arrays as much as possible so that as the initial dataset changes, the output automatically adjusts. Below is a sample with fictional data: Excel image

Notice how in cell G4, I'm using a dynamic formula to get the unique instances of Criteria1/2/3 with =UNIQUE(A4:C12). This produces a dynamic output in the area of G4:I10. Now I've used a simplistic/static approach in columns J & K by doing a basic SUMIFS() formula, which I had to manually drag down to row 10. I don't need the subtotals as shown below, I only included to show that the 'math' hangs together. What I really want instead is a dynamic array formula that I can type in cell J4 that would be a type of filter/sum... kind of like imagining SUM(FILTER(D4:E12,(A4:A12=G4:G10) * (B4:B12=H4:H10) * (C4:C12=I4:I10)), but of course this does not work.

I've seen some approaches that do a FILTER(D4:E12,ISNUMBER(MATCH(G4:G10,A4:A12,0)) * ISNUMBER(MATCH(H4:H10,B4:B12,0)) * ISNUMBER(MATCH(I4:I10,C4:C12,0))), but the results are filtered, not summed/consolidated. Hopefully I'm conveying what I'm trying to accomplish. In short, I want the results like in columns G:K, using only dynamic arrays to produce the results. If the solution involves MAKEARRY() and/or indirect references to cols G:I and/or 'helper' columns, I'm open to those approaches as well.

Solution

  • You have to use Byrow like this

    =BYROW(G4#,LAMBDA(r,
      SUMIFS(D4:D12,A4:A12,INDEX(r,,1),B4:B12,INDEX(r,,2),C4:C12,INDEX(r,,3)))
      )