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

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.

How do you know or where do you find the numbers that you want to check for?

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:


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.

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.