count certain numbers in a string of 12 numbers

[Marshall1970]

I have a field that contains the following: 012100002030

I need a formula that will tell me whether or not the string contains a number higher than 2 or whether or not it contains more than one 2.

Examples:

001000002011
111111000022
401110000000

the first loan would not meet the criteria as it contains no number higher than 2 and only contains one 2.
the second loan would meet the criteria as it contains two 2's.
the 3rd loan would meet the criteria as it contains a number higher than 2.

thank you, hopefully I have provided enough info.

 

[Oaktree]

Try:

=OR(SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0=2))>=2,SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0>2)))

 

[Marshall1970]

that works perfect. Thanks!

 

[Marshall1970]

one more thing, if I want the value to return "Yes" instead of TRUE or "No" instead of FALSE, how would I do that?

 

[PaddyD]

=IF( *insert formula *<INSERT formula>,"YES","No")

 

[Marshall1970]

ok, thanks all that works great. One more twist..

if the first digit is a 1, then I want to say "Exclude" regardless of any other digits
if the last digit is a 2 and all other digits are 0, then I want it to say "Include"

Examples:

000000000002 - include
111111111111 - include
200000300000 - exclude
100000000000 - exclude
000003000000 - exclude
000020000020 - exclude

 

[Oaktree]

Wrap two more IF statements around your formula:

=IF(LEFT(A1,1)+0=1,"Exclude",IF(RIGHT(A1,1)+0=2,"Include",IF( *insert formula *,"YES","No")))

 

[Marshall1970]

worked, thanks

 

[Marshall1970]

so close, if I include only the first if statement, it works. when i put in the 2nd if I get an errror msg..any ideas?


=IF(LEFT(AT2,1)+0=1,"IneligiblePmtHist",if(right(AT2,1)+0=2,"IneligiblePmtHist",IF(OR(SUMPRODUCT(--(MID(AT2,ROW(INDIRECT("1:"&LEN(AT2))),1)+0=2))>=2,SUMPRODUCT(--(MID(AT2,ROW(INDIRECT("1:"&LEN(AT2))),1)+0>2))),"IneligiblePmtHist",""))

 

[Oaktree]

My bad... it's exceeding the limit of 7 nested functions.

But, because they all return the same thing, you can combine them:

=IF(OR(LEFT(AT2,1)+0=1,RIGHT(AT2,1)+0=2,SUMPRODUCT(--(MID(AT2,ROW(INDIRECT("1:"&LEN(AT2))),1)+0=2))>=2,SUMPRODUCT(--(MID(AT2,ROW(INDIRECT("1:"&LEN(AT2))),1)+0>2))),"IneligiblePmtHist","")