Find numbers that repeat a certain number of times

I have a table with a few rows and two columns. I'm looking for a way to find numbers that repeat four (4) or more times in the table.


e.g. in the example below I have 22-4, 4-4 and 15-4. I want the 4 to go into the cell in row 9.

Currently I do this manually, which is ok for one spreadsheet, but I do this a number of times a day and it gets a bit tedious. The rest of my spreadsheet is pretty automated with only a few things I have to do manually, like inserting images into cells that I can't do with functions.


I have not yet been able to figure out how to check for these numbers that repeat four (4) or more times.


And if I have a table where there are two numbers that repeat four (4) or more times, then I would have another row for the second number. I guess this complicates matter again, but I guess I could have an if statement to check if the field is empty or if a number is already listed before populating the next cell?


Within this table I only work with numbers 1 – 22. I will never have a zero (0) and never have a number higher than 22... I don't know If that makes things easier at all?

MacBook Pro 14″

Posted on Mar 27, 2025 10:31 PM

Reply
Question marked as ⚠️ Top-ranking reply

Posted on Apr 8, 2025 9:37 AM

Just for completeness, with the recent (14.4) update, Numbers has added support for arrays, and the SORTBY() and TAKE() functions, specifically, solve this problem without the need for shenanigans, rounding, or any of that.


Taken from my earlier Table 2 that builds the COUNTIF()s, in the Summary table, set cell A2 to:


=TAKE(SORTBY(Table 2::A1:B22,Table 2::B1:B22−1),5,2)


The SORTBY() function takes the array A1:B22 and sorts it by the values in B1:B22. This creates an array of data sorted by frequency values in column B.

TAKE() then extracts the top 5 rows and 2 columns from that array, which automatically fills out the table.



So now it's now trivial to get the 'top 5', including duplicate values, but you'll still need a filter to limit the range to ≥4.

21 replies
Sort By: 

Apr 10, 2025 9:22 AM in response to SGIII

Well, Apple finally broke me down. I bought a new laptop today to replace my 10 yr old one. Next will be a Mac Studio (probably), after I decide on a display. I'm not all that enamored by Apple's 27" display, for the price, and their larger one is way overkill in features and price.

Reply

Apr 10, 2025 3:26 PM in response to Badunit

It's not quite the same, but Numbers on iCloud has been updated to support the new array and lambda functions, so you could use that to get familiar with the new functions/features, even while your desktop system doesn't support them.

Reply

Find numbers that repeat a certain number of times

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.