Forum Discussion
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...
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)) )
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
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
Not sure I really understand
Please post a picture reflecting what you expect to add later & what the result should look like
Lorenzo 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
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 (https://support.microsoft.com/en-us/office/how-to-correct-a-spill-error-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023)
