SOLVED

# Count and show multiple numbers who appear in an array

Copper Contributor

# 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 numbers which appear twice or even more and how many times.  I tried vlookup counta and so on...all in vain. So need a function who can scan the array ranging from AN:95 to AX107 and returns the "number" and "count" of those cells. Many thanks Sandro

10 Replies

# Re: Count and show multiple numbers who appear in an array

One way with 365:

in AZ95:

``````=LET(
range,  AN95:AX107,
tocol,  SORT(TOCOL(CHOOSECOLS(range, SEQUENCE(,3,3,4)),1)),
freq,   DROP(FREQUENCY(tocol,tocol),-1),
VSTACK({"Number","Count"}, FILTER(HSTACK(tocol,freq), freq))
)``````

# Re: Count and show multiple numbers who appear in an array

Dear L z,
thx for the quick response...I tried your example and it works like a charm. However when new values will be put in into the array left hand side how can they be shown in the array on the right hand side? Do I need to expand the right hand array (which is my result area) and provide some empty cells within the array ? Thx again for your help. Sandro

# Re: Count and show multiple numbers who appear in an array

Not sure I really understand

Please post a picture reflecting what you expect to add later & what the result should look like

# Re: Count and show multiple numbers who appear in an array

@L z. When I put in new unknown values the function stops working. Pls see the screenshot. The red numbers are new so for 529 I need to get a 2 and for 856 a 1. Short and sweet:" how to handle new unknown values" ?Thx again Sandro

# Re: Count and show multiple numbers who appear in an array

@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)))))))``````

# Re: Count and show multiple numbers who appear in an array

This works no problem here. No change in the formula:

Make sure there's enough empty cells below AZ94 & BA94 to allow the formula to spill, otherwise you get a #SPILL error, as in your picture (How to correct a #SPILL! error)

# Re: Count and show multiple numbers who appear in an array

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

# Re: Count and show multiple numbers who appear in an array

@L z. Oh! Yes. I do not look that so carefully. Thanks for catching that.

# Re: Count and show multiple numbers who appear in an array

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)``

best response confirmed by sandrosg (Copper Contributor)
Solution

# Re: Count and show multiple numbers who appear in an array

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))
)``````
1 best response

Accepted Solutions
best response confirmed by sandrosg (Copper Contributor)
Solution

# Re: Count and show multiple numbers who appear in an array

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))
)``````