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

jmallory76

New member
Joined
Aug 20, 2013
Messages
2
Reaction score
0
Points
0
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.
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
How do you know or where do you find the numbers that you want to check for?
 

jmallory76

New member
Joined
Aug 20, 2013
Messages
2
Reaction score
0
Points
0
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.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 
Top