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))
)
Patrick2788's avatar
Patrick2788
Silver Contributor
Dec 17, 2022

sandrosg 

Here's my rendition using REDUCE to deliver the solution.

 

Defined name 'Numbers'

=Sheet1!$AN$95:$AX$107

 'NVector - the above is converted to a vector, 0s pulled, and sorted.

=SORT(TOCOL(numbers, 1))

Lamba 'NStack.  Parameters setup to be used with REDUCE.  'k' pulls all instances of a given number.  'n' obtains a unique value from 'k'.  VSTACK - stack the accumulated value of 'a' with an HSTACK which includes the number and its count.

=LAMBDA(a,v,LET(k, FILTER(NVector, NVector = v), n, UNIQUE(k), VSTACK(a, HSTACK(n, COUNT(k)))))

Sheet level formula:

=REDUCE({"Number","Count"},UNIQUE(NVector),NStack)

 

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    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))
)

Resources