Forum Discussion

vernico2023's avatar
vernico2023
Copper Contributor
Jun 13, 2024

data matching multiple criteria to be combined

Hello, trying to figure out how to use formulas in Excel to process the following data (top rows in table) and get to the desired result (lower rows in table). Any help is much appreciated. Thank you...
Formulas and Functions
djclements's avatar
djclements
Silver Contributor
Jun 14, 2024

vernico2023 If you're using Excel for MS365, you could try any one of the following single-cell dynamic array formulas...

 

(1) Compact summary, without blank columns:

 

=LET(
    rng, B2:K3,
    arr, UNIQUE(rng, TRUE),
    VSTACK(arr, SUMIFS(B4:K4, TAKE(rng, 1), TAKE(arr, 1), TAKE(rng,-1), TAKE(arr,-1)))
)

 

(2) Desired results, as per your sample:

 

=LET(
    rng, B2:K3,
    item, TAKE(rng, 1),
    type, TAKE(rng,-1),
    arr, UNIQUE(rng, TRUE),
    items, TAKE(arr, 1),
    types, TAKE(arr,-1),
    results, VSTACK(arr, SUMIFS(B4:K4, item, items, type, types)),
    ids, XMATCH(items & "|" & types, item & "|" & type),
    n, COLUMNS(rng),
    cols, SEQUENCE(, n),
    by_arr, HSTACK(ids, FILTER(cols, ISNA(XMATCH(cols, ids)))),
    SORTBY(EXPAND(results,, n, ""), by_arr)
)

 

(3) Pivoted summary:

 

=LET(
    rng, B2:K4,
    item, TAKE(rng, 1),
    type, INDEX(rng, 2, 0),
    num, TAKE(rng,-1),
    items, SORT(UNIQUE(item, TRUE),,, TRUE),
    types, SORT(UNIQUE(TOCOL(type))),
    VSTACK(
        HSTACK("Sum of Number", items),
        HSTACK(types, SUMIFS(num, item, items, type, types))
    )
)

 

See attached...