Search code examples
arraysexcelfilterexcel-formuladynamic

Combining Excel FILTER() and SUM() Functions

As efficiently as possible, I am wanting to take a tabular dataset in Excel where there are (purposefully) duplicate identifiers to records and consolidate this to a unique/summed dataset (very common in the type of work that I do).

I can of course do this with SUMIFS() and script a VBA-based solution, but I would really like to accomplish this with Excel's dynamic array functions (calculates in real time and no need for macro-enabled workbooks).

Attached is a visual:

enter image description here

Less evident here is that I used UNIQUE() in cell H4 (thus the reference to H4# in formula as shown in cell J4). In the LET() statement, I am defining the source data as the values, and the identifiers as the color/type, which I then concatenate into a single string that I call 'srcKey'.

The unique combinations of these are then in the array H4#, which I capture as 'fltrKey'. My desired output would be to retain the column breakout (hence the BYCOL() function), but sum the rows where the fltrKey is found within the srcKey. This did not produce the correct results so I changed from fltrKey to direct cell references of H4 and I4 respectively (just as a test).

This gets me the right summed values for blue birds, but I want a dynamic formula that would have done this for each combination in my fltrKey.

It may be that my method here (using the FILTER() with ISNUMBER() and SEARCH() is not the appropriate approach). I can get the intended results if just doing 1 column of the source values at a time, but that's not ideal/preferred. I'm almost confident Excel can handle this, I just need help getting the methodology right.

Solution

  • One way is to use MAKEARRAY:

    =LET(
    clr,B4:B10,
    typ,C4:C10,
    unq,H4#,
    val,D4:F10,
    MAKEARRAY(ROWS(unq),COLUMNS(val),LAMBDA(z,y,SUMIFS(INDEX(val,0,y),clr,INDEX(TAKE(unq,,1),z),typ,INDEX(TAKE(unq,,-1),z)))))

    enter image description here

    One note:

    Once GROUPBY is widely available it can be used here:

    =GROUPBY(B3:C10,D3:F10,SUM,3,0)

    enter image description here