Share via

Excel formula support

Jonathan Scanlon 20 Reputation points
2026-02-09T15:05:50.1166667+00:00

I need a formula to count the number of times a number appears in a list, for example, 1, 2, 2, 3. how would I count the number of times the 1, 2 and 3 appear in a simple formula? These numbers are all in a single cell.

Microsoft 365 and Office | Excel | For business | Windows

Answer accepted by question author

  1. IlirU 2,251 Reputation points Volunteer Moderator
    2026-02-10T15:47:29.4633333+00:00

    User's image

    In cell L2 apply below formula:

    =BYROW(J2:J14, LAMBDA(a, SUM(--(TOCOL(--TEXTSPLIT(TEXTJOIN(";",, F3:F15), ",", ";"), 3) = a))))

    Hope this helps.

    IlirU

    1 person found this answer helpful.

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. IlirU 2,251 Reputation points Volunteer Moderator
    2026-02-10T10:00:20.67+00:00

    User's image

    @ Jonathan Scanlon,

    Use below formula in cell C2:

    =LET(d, --TOCOL(TEXTSPLIT(TEXTJOIN(";",, A2:A9), ",", ";")), GROUPBY(d, d, COUNT,, 0))

    If the answer is helpful, please click "Accept Answer" and kindly upvote it.

    IlirU

  2. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2026-02-09T23:28:34.8466667+00:00

    Hi,

    In cell C2, enter this formula

    =LET(t,TOCOL(TEXTSPLIT(A2,",")),GROUPBY(t,t,COUNTA,,0))

    Hope this helps.

    User's image

    0 comments
  3. Hendrix-C 15,005 Reputation points Microsoft External Staff Moderator
    2026-02-09T16:33:10.5733333+00:00

    Hi @Jonathan Scanlon,

    Thank you for posting your question in the Microsoft Q&A forum.

    According to your concern, count the number of times a number appears in a list in a single cell, you can use this formula:

    • For counting one number (e.g. 1): =LET(x,--TEXTSPLIT(A3,","),SUM(--(x=1)))

    User's image

    • For counting all three numbers in one formula: =LET(x,--TEXTSPLIT(A3,","),nums,{1,2,3},MAP(nums,LAMBDA(n,SUM(--(x=n)))))

    User's image

    • You can easily apply the formula to the whole range by copying the formula in the first cell and pasting into other cells.

    User's image

    I hope this helps you move forward smoothly. If you have any questions or need further assistance, please feel free to reach out again on this post so I can continue to support you.  

    Thank you for your patience and understanding. Looking forward to your response.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".       

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.   

  4. Q&A Assist
    2026-02-09T15:06:03.1433333+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    To count the number of times specific numbers appear in a list contained within a single cell in Excel, you can use the COUNTIF function. However, since the numbers are in a single cell, you will first need to split them into separate values. Here’s a step-by-step approach:

    1. Split the numbers: If the numbers are separated by commas, you can use the TEXTSPLIT function (available in Excel 365) to split them into an array.
    2. Count occurrences: You can then use COUNTIF to count how many times each number appears.

    For example, if your numbers are in cell A1:

    =COUNTIF(TEXTSPLIT(A1, ","), "1")  
=COUNTIF(TEXTSPLIT(A1, ","), "2")  
=COUNTIF(TEXTSPLIT(A1, ","), "3")

    This will give you the count of how many times 1, 2, and 3 appear in the list.

    If you are using an older version of Excel that does not support TEXTSPLIT, you would need to manually separate the values into different cells or use a more complex formula with array functions.

    References:

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.