SOLVED

Contributor

# Are realized spills a different type of thing than the same result calculated in LET?

When I use the # construct to utilize realized spilled output as input into SUMIF it works, but if I use the same data calculated in LET it does not. I assume this means the realized spill is one type of data and the result of the formula that generated the spilled data is a different type of data. Is that true? What is the difference? Here is an example.

4 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Are realized spills a different type of thing than the same result calculated in LET?

In this part

SUMIFS(values,names,C17#)

you try to use array (values) as sum range. SUMIFS works only with ranges here, not arrays.

# Re: Are realized spills a different type of thing than the same result calculated in LET?

As variant that could be

``````=LET(
names,  UNIQUE(C3:C6),
namesWithYes, FILTER(C3:C6, E3:E6="yes"),
valuesWithYes, FILTER(D3:D6, E3:E6="yes"),
totals,  MMULT(--(names=TRANSPOSE(namesWithYes) ), valuesWithYes),
IF( {1,0}, names, totals )
)``````

# Re: Are realized spills a different type of thing than the same result calculated in LET?

Thanks Sergei. This was the first moment that I understood "C17#" is not an array. Also thank you for the formula, I would never have figured that out.