Looking for a value in certain select columns within a larger range

[mcgonma]

In columns AX-DI there is a formula that are returns a value of "FALSE" or "TRUE".
In column AF, I am trying to search across only certain select columns within AX-DI to find if these specific columns have a "FALSE" value. If any one of them does, return a "YES" in column AF.
This is the formula that I am using, but it is either not the correct formula or I'm not using it correctly. I know this because I know that DC20557 has a "FALSE" value but it is not returning a "YES" in AF.

=IF(COUNTIFS(BA20557,BC20557,BH20557,BI20557,BL20557,BU20557,CC20557,CD20557,CF20557,CG20557,CJ20557,CL20557,CN20557,CR20557,CV20557,CW20557,DC20557,FALSE),"YES","-")

[FDibbins]

Perhaps if you just used COUNTIF?
=IF(COUNTIF(AX20557:DI20557,FALSE),"YES","-")

Using countifS like that, it is testing for instance, to see if the BA20557 contains whatever is in BC20557, then testing to see if BH20557 contains what is in BI20557 etc
=countifs(criteria-range1, criteria1, criteria-range2, criteria2....etc)

[Sam Capricci]

I notice Ford gave you an answer already but since I worked on it too I'll add my 2 cents...

with countif or countifs you need a criteria range which is contiguous like =if(countif(BA20557:DC20557,FALSE)>0,"yes","-")
is there a reason you can't use a contiguous range?

AND, I notice you don't need the >0 part of mine so Ford got it all for you.

[mcgonma]

The contiguous range will not work for me because columns AX-DI represent a complete set of identification criteria about a person that I'm looking to see if they are met; so for each column within AX-DI, if the person does meet the criteria of the respective column the formula returns a result of "FALSE".
So now, what I am looking to do with the formula in AF is find people who meet a specific subset of criteria from within the larger set in AX-DI. In this particular example, the subset is the criteria of columns BA,BC,BH,BI,BL,BU,CC,CD,CF,CG,CJ,CL,CN,CR,CV,CW,DC

[FDibbins]

What will be in the alternate columns? If is not a T/F, then it really doesnt matter

[mcgonma]

Someone may have a "FALSE" in the columns not specifically identified, but that does not make them a selection for the sub-group that I'm trying to identify.
So, just because they have a "FALSE" in AZ, BB, BM, BN, CA... does not mean that I want them to show up in the results.

[Sam Capricci]

this looks like it works, it is kinda ugly but it worked in my test file...
=IF(COUNTIF(BA20557,FALSE)+COUNTIF(BC20557,FALSE)+COUNTIF(BH20557:BI20557,FALSE)+COUNTIF(BL20557,FALSE)+COUNTIF(BU20557,FALSE)+COUNTIF(CC20557:CD20557,FALSE)+COUNTIF(CF20557:CG20557,FALSE)+COUNTIF(CJ20557,FALSE)+COUNTIF(CL20557,FALSE)+COUNTIF(CN20557,FALSE)+COUNTIF(CR20557,FALSE)+COUNTIF(CV20557:CW20557,FALSE)+COUNTIF(DC20557,FALSE)>0,"YES","-")

[Nardar]

I want to do the same thing as mcgonna. In my case, I'm using text (the letter x) rather than a formula in some records and not others.

Questions:

1. Do I need to identify "x" in the above function, or does it work regardless of the cell content?

2. Am I correct that I need to add an additional column to indicate which records fit the criteria?

3. If so, and I then want to sort the identified records by last name, for instance, do i sort on the new column and then last name?

4. How do I put the resultant sort on a new worksheet? Cell references? Copy/paste?

Thank you.

[FDibbins]

Nader, Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[Nardar]

Ok. Thanks.

How do I make the link?