Forum Discussion

sandrosg's avatar
sandrosg
Copper Contributor
Dec 17, 2022
Solved

Count and show multiple numbers who appear in an array

Hi there, I'm new to this community and tried searching the net and other support pages for a rather simple task. I got an array of numbers such as seen in the screenshot. I need to show those number...
excel
  • Lorenzo's avatar
    Lorenzo
    Dec 17, 2022

    With SCAN

     

    =LET(
  range,  AN95:AX107,
  tocol,  TOCOL(CHOOSECOLS(range, SEQUENCE(,(COLUMNS(range)+1)/4,3,4)),1),
  uniq,   SORT(UNIQUE(tocol)),
  freq,   SCAN(0,uniq, LAMBDA(seed,number, COUNT(FILTER(tocol,tocol=number)))),
  VSTACK({"Number","Count"}, HSTACK(uniq,freq))
)
Harun24HR's avatar
Harun24HR
Bronze Contributor
Dec 17, 2022

sandrosg TOCOL() itself can be used as more dynamic using option 1 means ignore blanks.

 

 

=LET(x,TOCOL(AO95:AX100000,1),y,SORT(UNIQUE(x)),
VSTACK({"Number","Count"},HSTACK(y,BYROW(y,LAMBDA(p,SUM(--(x=p)))))))

 

 

 

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 17, 2022

    Harun24HR 

    I didn't take the TOCOL(array,1) route as in the initial picture there's a number of non-empty cells - Those in purple with 00:00

     

    Hope this makes sense

    • Harun24HR's avatar
      Harun24HR
      Bronze Contributor
      Dec 17, 2022
      Lorenzo Oh! Yes. I do not look that so carefully. Thanks for catching that.

Resources