excelsumifs

using SUMIF with array as argument

I need to get the sum of a column for each unique value on another 2 columns independently. as in the following example:

test sheet

I need the sum of "num" for each unique value in "mod" where the concatenation of "term" and "desc" are also not duplicate

The highlighted Range above has the expected result, but its using the range in column 10 which is an auxiliary column that cannot be used.

the formula from R2C10 is the following:

=INDEX(R2C4:R6C4;XMATCH(R2C8:R5C8;R2C2:R6C2 & " - " & R2C3:R6C3);1)

(It is using column 8 which must also be excluded but I can do it without it just fine in one go with a LET and UNIQUE. It's just harder to read)

What I need to do is change the range R2C10:R5C10 in the following SUMIF for the index formula above that generated the values in said Range, but when I do it I get the spill to the right with #VALUE!

=SUMIF(R2C10:R5C10;R10C1:R12C1;R2C1:R6C1)

I´ve seen some videos of people using SUMIF or SUMIFS with INDEX arrays as arguments, but I must be doing something wrong.

How should I proceed to do this?

Solution

  • You could try using the following formula:

    enter image description here

    • Formula used in cell B8

    =SUM(TAKE(UNIQUE(FILTER(A$2:C$6,A8=D$2:D$6)),,1))

    • Or using one single dynamic array formula:

    =MAP(A8#,LAMBDA(x,SUM(TAKE(UNIQUE(FILTER(A2:C6,x=D2:D6)),,1))))

    Or, if the duplicate values have different num, then this should be a better alternative as far I think,,

    enter image description here

    =MAP(A8#,LAMBDA(x,
 LET(
     a, FILTER(HSTACK(B2:B6&" "&C2:C6,A2:A6),D2:D6=x),
     b, TAKE(a,,1),
     c, UNIQUE(b),
     SUM(VLOOKUP(c,a,2,0)))))