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