How to find values that repeat x times

Copper Contributor

How to find values that repeat x times

Hello to everybody!

Help me figure out if it is possible to find cells in Excel using built-in tools or functions that are repeated x times. I have a large book of clients and am currently working on a loyalty program. I need to know which of them made 5 purchases, which made 7, and which made 10. I know that it is possible to set the value of a cell and see how many times it appears in the document. But I have more than 3,000 clients, and searching for information on each one is a huge job. So I would like to enter a query like "Find cells whose value is repeated 10 times" - and see the results.

Thank you a lot!

4 Replies

Re: How to find values that repeat x times

Hi @Svetik,

You can try to use COUNTIF function to achieve your goal:

COUNTIF function - Microsoft Support

Organize all your client data for example in a single column. Let's say your list starts from cell A1.

2: Count the Occurrences

In any empty cell, use the `COUNTIF` function to count how many times a specific value repeats in your data. For instance, if you want to find clients who made 10 purchases:

``=COUNTIF(A:A, "10")``

* you can replace "10" with the value you want and then press Enter to get the result (count).

This formula tells Excel to count how many times "10" appears in column A.

3: Filter the Results

To filter clients who made (for example) exactly 10 purchases, follow these steps:

1. Click on the header of the column containing your client data (e.g., column A).

2. Go to the "Data" tab in Excel.

3. Click on the Filter icon on the column header you selected.

5. From the submenu, select "Equals" or "Custom Filter," depending on your preference.

6. In the dialog box that pops up, enter the count you obtained from the `COUNTIF` function (e.g., 10).

7. Click "OK" or "Filter."

Excel will now display only the rows where clients made exactly 10 purchases. You can repeat these steps for different purchase counts, such as 5 and 7, by adjusting the value in the `COUNTIF` formula and applying the filter accordingly.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic

Re: How to find values that repeat x times

Thanks a lot! Try it today!

Re: How to find values that repeat x times

=TEXTJOIN(", ",,DROP(REDUCE("",SEQUENCE(ROWS(UNIQUE(A1:A15))),

LAMBDA(x,y,VSTACK(x,IF(COUNTIF(A1:A15,INDEX(UNIQUE(A1:A15),y))=C1,

INDEX(UNIQUE(A1:A15),y),"")))),1))

With Office 365 or Excel for the web you can apply this formula.

Re: How to find values that repeat x times

PivotTable could work as

and you may filter it on value >=10