A second more challenging goal is to create the table of results seen in E7:F12.
For convenience,datais thenamed rangeB5:B16.
Instead, the colors exist in anarrayreturned by the TEXTBEFORE function.SUMIF requires a rangefor therangeargument.
TheLET functionis used to assign intermediate results to named variables.
This array is then multiplied by all numbers (n).
SUM then returns the result for that row.
However, it does no harm and perhaps makes the pattern of this formula easier to understand.
So the double negative allows the count variation of the formula to work without further adjustment.
HSTACK joinsarray1andarray2together horizontally, and returns a 2-column array as a final result.
TEXTAFTER Function
The Excel TEXTAFTER function returns the text that occurs after a given substring or delimiter.
BYROW can apply stock functions like SUM, COUNT, and AVERAGE or a custom LAMBDA function.
All results are returned at the same time in a single array….
