Results 1 to 4 of 4

Thread: Count amount of desired numbers in a single cell and return that value in another cel

  1. #1

    Count amount of desired numbers in a single cell and return that value in another cel



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I'm having trouble with this, and hoping someone can point me in the right direction with a formula.

    For instance A1 contains the following numbers:

    15;17;20;45;48;61;69;72;76;77;80

    I would like a formula that would see that A1 contains the values 15, 20, 45 and 69 for example and return the amount of matches found. So in cell B1, I would get the value 4 (the amount of my desired numbers found).

    If I only requested to see if A1 contains the values 17 and 45...B1 would return the value 2 with my desired formula.


    Thank you in advance for any help or insight.

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    719
    Articles
    0
    Excel Version
    Excel 2010 64bit
    How do you know or where do you find the numbers that you want to check for?

  3. #3
    The numbers I'm looking for will always be different based on different data I need to look at. But for example, here are a few rows of numbers:

    15;17;20;45;48;61;69;72;76;77;80
    15;17;20;45;48;61;69;72;76;77;80
    15;17;19;20;61;69;72;76;77;80;86
    15;17;20;48;61;69;72;76;77;80;86
    15;19;20;45;48;61;69;72;76;77;86
    15;20;48;49;61;69;72;76;77;80;86
    15;19;20;45;48;61;69;72;76;77;86
    15;17;20;61;69;72;76;77;80;82;86
    15;17;20;45;61;69;72;76;77;80;82
    15;17;19;20;61;69;72;76;77;80;86
    15;17;19;20;61;69;72;76;77;80;86
    15;17;20;45;48;61;69;72;76;77;80
    15;17;19;20;61;69;72;76;77;80;86

    Let's say I want to see if each row of cells contains the following numbers...15, 17, 20, 86. So in the first cell of data I provided the value I would like returned is 3 because it contains 3 of my desired numbers. In the third row, C1, for example, I would want the value 4 returned because that cell contains 4 of my desired numbers.

    Thanks so much for your help on this, I can't wrap my head around how to make a formula for this.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,490
    Articles
    0
    Excel Version
    Excel 2016
    Assuming the numbers you want to look up in L1:L4 and that they are all 2 digits as per your sample, then try

    =SUMPRODUCT(COUNTIF(A1,"*"&$L$1:$L$4&"*"))

    Note: Your L1:L4 range should not contain blanks or single digits. You could create a dynamic named range so that you can add or remove as necessary.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •