CODEWITHSUNDEEP
excelif-statementexcel-formula
kit99
kit99

Reputation: 187

Excel IF-formula with multiple conditions from 4 columns

Got values (people's age) in columns B, C, D and E. I'm trying to get output "2" if age is below 23 or above 67. But I have to take into account that there may be celles present with text and maybe cells holding value 0, or even blank. So I got this formula for column B:

=IF(ISNUMBER(B2),IF(OR(AND(B2>0,B2<23),B2>67),2,""),"")

Formula works, but I want to do the same thing with columns C, D and E - and put it all into one formula. This should have output "2" if any of the 4 columns meet my age-conditions. And my problem is that I clearly don't have the skills to pull this off... Anyone that can help me out?

Upvotes: 0

Views: 483

Answers (2)

toucansame
toucansame

Reputation: 164

Edit 2 - Added support for non-contiguous range. This is not an array formula"

=IF(OR(AND(N(B1)>0,N(B1)<23),AND(N(F1)>0,N(F1)<23),AND(N(L1)>0,N(L1)<23),AND(N(X1)>0,N(X1)<23),MAX(B1,F1,L1,X1)>67),2,0)

***Edited to account for possiblity that all four cells may be text, blank, 0 or a combination of the 3.

Here is an array formula that takes into account the text, blank, and zero cells that may exist in the cells:

=IF(SUM((B1:E1=0)+ISBLANK(B1:E1)+ISTEXT(B1:E1))>3,0,IF(OR(MAX(IF(B1:E1=0+ISBLANK(B1:E1)+ISTEXT(B1:E1),60,B1:E1))>62,MIN(IF(B1:E1=0+ISBLANK(B1:E1)+ISTEXT(B1:E1),60,B1:E1))<23),2,0))

Make sure to enter this formula using CTRL+SHIFT+ENTER, otherwise it will not work.

Upvotes: 1

cybernetic.nomad
cybernetic.nomad

Reputation: 6418

If it doesn't matter which age is above 67 or below 23 you could use:

=IF(OR(MAX(B2:E2)>67,MIN(B2:E2)<23),2,"")

Upvotes: 1

Related Questions