Formula for return a "1" if certain numbers are found

mikerickson

MrExcel MVP

Joined

Jan 15, 2007

Messages

24,355

• Nov 29, 2013

• #2

Try =SIGN(SUMPRODUCT(COUNTIF(AC1:AC500, {26, 52, 35, 134})))

The explicit array can be replaced with a cell vector containing the numbers of interest.

 

Upvote 0

C

cl604

New Member

Joined

Nov 29, 2013

Messages

31

• Nov 29, 2013

• #3

mikerickson said:

Try =SIGN(SUMPRODUCT(COUNTIF(AC1:AC500, {26, 52, 35, 134})))

The explicit array can be replaced with a cell vector containing the numbers of interest.

Click to expand...

Great work!!

Works perfect.

 

Upvote 0

C

cl604

New Member

Joined

Nov 29, 2013

Messages

31

• Nov 30, 2013

• #4

Any ideas how i can reverse this so if A certain number is found IE 26,52,29,11 then 0 is produced otherwise 1 is produced?

Thanks for your help

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,463

Office Version

1. 365

Platform

1. Windows

• Nov 30, 2013

• #5

cl604 said:

Any ideas how i can reverse this so if A certain number is found IE 26,52,29,11 then 0 is produced otherwise 1 is produced?

Click to expand...

Subtract the formula Mike gave you from 1...

=1-SIGN(SUMPRODUCT(COUNTIF(AC1:AC500, {26, 52, 35, 134})))

 

Upvote 0

C

cl604

New Member

Joined

Nov 29, 2013

Messages

31

• Nov 30, 2013

• #6

You guys are legends

Thanks

 

Upvote 0

C

cl604

New Member

Joined

Nov 29, 2013

Messages

31

• Jan 7, 2014

• #7

Hi guys,
I have a new problem.
Using the above formula solved a lot of issues but i need to find a sequence of 3 specific numbers out of about 300 that might appear. So the sequence has to be 26,199,10. 26 must appear first then 199 then 10.How can i incorporate it into this formula above.
Thanks in advance.

 

Upvote 0

mikerickson

MrExcel MVP

Joined

Jan 15, 2007

Messages

24,355

• Jan 7, 2014

• #8

=SUMPRODUCT(A1:A100=26,A2:A101=199,A3:A102=10.26) will return the number of 26, 299, 10.26 sequences in A1:A102

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,463

Office Version

1. 365

Platform

1. Windows

• Jan 7, 2014

• #9

cl604 said:

I have a new problem.
Using the above formula solved a lot of issues but i need to find a sequence of 3 specific numbers out of about 300 that might appear. So the sequence has to be 26,199,10. 26 must appear first then 199 then 10.How can i incorporate it into this formula above.

Click to expand...

Your question is not clear to me...

1) Do those three numbers have to follow each other one cell immediately under the other (e.g.,AC4=26, AC5=199, AC6=10) or could there be other cell between them as long as they are in that order (e.g., AC4=26, AC17=199, AC26=10)?

2) I presume each of those three numbers should be marked with a 1, correct?

3) What about the other numbers you listed in your original message... should they still be marked with a 1 also, or for this question are we talking about those three numbers only?

 

Upvote 0

C

cl604

New Member

Joined

Nov 29, 2013

Messages

31

• Jan 7, 2014

• #10

Cheers thanks for that. The only problem is the sequences don't appear one after another. IE the number 26 might appear at a 78, 199 might appear at 50 and 10 might come at 5. Once that sequence appears a 1 must be produced.Just like this =SIGN(SUMPRODUCT(COUNTIF(AC1:AC500, {26, 199,10}))). Except the sequence must be exact.

Thanks great work

 

Upvote 0