OK, It’s finally dawned on me I need your help STW. Just like Princess Leah implored Obi Wan Kenobi in Starwars.

I have a column of numbers, in cells A1 to A10 if you wish, and these can take an integer value of between 0 and 3.

If any of these cells are value 1 or 2, then I want a single cell to return the value 1, which I’m using as a switch for another action elsewhere. If 1 or 2 don’t appear, (all values logically are 0 or 3) then the ‘switch’ value should be 0.

I’ve tried various combinations of IF with combinations of COUNTIF, AND and OR logical tests but just can’t get it right.

Thanks, in anticipation.

=IF(OR(A1=1,A1=2),1,0)

Something like =if (OR(A1="1", A1="2"), "1","0") perhaps?

EDIT: Jinx! (You’re returning the wrong value there though.)

=SUMIFS(A1:A10, A1:A10, “=1”, A1:A10, “=2”)

*I think*

I usually use an extra column for this:

=OR(IF(test for 1), IF(test for 2))

Then have a sum or check cell on that column.

Use two other cells( B1 and B2 for example) to do =countif( A1:A10,”1″) and =countif(A1:A10,”2″)

In you “Switch” cell do =if(sum (b1:b2)>0, 1, 0)

Alternative to above would be
=IF((COUNTIF(A1:A10,”=1″)+COUNTIF(A1:A10,”=2″))>0,1,0)

.

Excel help. Can’t get the right cominaiton of funcitons to do what I want.

…also, pressing f7 will start the spell checker. 😉

Do you want a result in each row, or do you mean if it appears at all within A1 to a10?

Ah, sorry, I’ve just re-read it realised you want one cell in total, not one for each row.

Suppose you could still use my method, then add up the column and return 1 is the sum is greater than zero. Bit inelegant though.

=OR(MAX(A1:A7)>2,MIN(A1:A7)<1)

or if you want an actual figure just multiply by one

=OR(MAX(A1:A7)>2,MIN(A1:A7)<1)*1

just seen they are the wrong way, so NOT them

=NOT(OR(MAX(A1:A7)>2,MIN(A1:A7)<1))

oops, now i get you… ignore the above and look below

=OR(IF(test for 1), IF(test for 2))

Is there any practical difference in nesting IFs in an OR or ORs in an IF, out of interest? Anyone know?

Is there any practical difference in nesting IFs in an OR or ORs in an IF, out of interest? Anyone know?

Yes.

Mixing different functions in the same formula makes my brain itch.

I like to see all the seperate steps happen in their own cells if I can.

It’s not as if i’m going to run out of cells.

=OR(COUNTIF(A1:E7,1),COUNTIF(A1:E7,2))

basically, you are looking to see if there is a 1 or 2
so, count them, then OR the results

and as before, if you need an integer result, multiply by 1

=OR(COUNTIF(A1:E7,1),COUNTIF(A1:E7,2))*1

In column B I’d put this:

=IF(A2=1,1,IF(A2=2,1,0))

Then elsewhere, in your “single cell” put this:

=IF(SUM(B2:B11)>0,1,0)

Blimey, I only popped out on site for half an hour, and that’s given me plenty to go at! Ta.

…also, pressing f7 will start the spell checker.

😆 Thanks Perchy.

or you could use COUNTIFS()

=COUNTIFS(A1:A7,”>0″,A1:A7,”<3″)

which would count them for you

If you don’t want the number of instances in your switch cell, combine with an IF statement:
=IF(COUNTIF(A1:A8,1)+COUNTIF(A1:A8,2)>0,”1 or 2″,”No 1 or 2″)

not sure what that method buys you over…

=OR(COUNTIF(A1:E7,1),COUNTIF(A1:E7,2))*1

You need to use an array formula. Type:

=IF(OR(A1:A10=1,A1:A10=2),1,0)

But instead of just pressing ENTER, press CTRL+SHIFT+ENTER.

nice, sparkov, but the ‘if’ statement is unnecessary?
oh, ok, i see, it’s where i use ‘*1’

Yeah, otherwise it just returns ‘true’ or ‘false’.

Edit: I didn’t realise you could also just multiply it by 1. Guess I’ve learned something too!

sparkov has it. Good grief you should have seen some of the monstrous formulae I was trying, but that array formula seems to be the thing. Cheers, I’m going to learn a bit more about them.

FWIW, you can use — (two minus signs) in front of it instead of multiplying by one. IIRC it’s slightly faster due to some vagaries of how Excel works in the background.

=–OR(COUNTIF(A1:A10,1),COUNTIF(A1:A10,2))