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)


In this part


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


As variant that could be

  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.