SOLVED

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

Contributor

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

@boukasa 

In this part

SUMIFS(values,names,C17#)

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

@boukasa 

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 )
)
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.