Finding EVEN numbers within a three number cell

I'm looking to find two or more "even" numbers (2, 4, 6 or 8) within a three number cell.

5 examples below show 4 numbers with two or more even numbers.

247
386
592
468
861

I've been using the following code to find double numbers with a series of cells.

=SUMPRODUCT(--(FREQUENCY(((LEN(E28:E72)-LEN(SUBSTITUTE(E28:E72,MID(E28:E72,{1,2,3},1),"")))=2)*ROW(E28:E72),ROW(E28:E72))=2))

I believe this formula would work but I'm striking out, any recommendations?

So are you looking to do this for each cell or for the whole range at once... and if it is the ladder then, are you trying to find 2 or more 3-digit numbers that contain 2 even digits... not sure what you mean?

If it is the former, then perhaps: =SUMPRODUCT(--(MOD(MID(A1,ROW(INDIRECT("1:3")),1),2)=0))>=2

copied down.. where A1 is first 3-dig number.

You can always use the formula I gave you as a helper column, then count the TRUEs with:

=COUNTIF(F28:F72,TRUE) assuming you put the helper in F28:F72

If you wanted a single cell calculation:

Please Login or Register  to view this content.

though I'm fairly sure that with thought the above could be shortened

but that's counting zero too, isn't it? For just 2, 4, 6, 8 try

=SUMPRODUCT((MMULT(LEN(E28:E72)-LEN(SUBSTITUTE(E28:E72,{2,4,6,8},"")),{1;1;1;1})>1)+0)

Based on a prior thread I think 0 is meant to be treated as even...that said I'm no doubt wrong ... more to the point I can't find the prior thread as I'm sure I've seen this previously.

Found it...

http://www.excelforum.com/excel-gene...-a-column.html

posts 7 onwards

OK, I see DO......

my previous suggestion can be edited, just include a zero in the first array and an extra 1 in the second......

=SUMPRODUCT((MMULT(LEN(E28:E72)-LEN(SUBSTITUTE(E28:E72,{0,2,4,6,8},"")),{1;1;1;1;1})>1)+0)

...which would be an approach you could use to count any combination of digits......or for even digits (including zero) try

=SUMPRODUCT((MMULT(1-MOD(MID(E28:E72&111,{1,2,3},1),2),{1;1;1})>1)+0)

So, in the end it wouldn't it be easier to use the helper column.. at least most average users would be able to understand what is going in even 10 years from now