Finding a value in select columns within a large 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","-")

 

[jproffer]

Countifs works like: Range, Criteria, Range, Criteria, etc, etc....all I see in your formula is a bunch of ranges, and then "FALSE" which I assume is your criteria.

However, it might be helpful to know that in excel False has a value of 0, and True has a value of 1 (assuming they're not text values that you entered into your formulas). So SUM(A:A) will return 0 if ALL cells are false, and will return some number greater than zero if ANY of the cells are True.

By text values vs. "regular" true/false values, I mean...lets say you put this:

=A1=4

If A1 is 4, it will return True (not text, just a value of true)...if you put:

=IF(A1=4,"True","False")

Then it will return True, but as text. I'm not sure whether text values of "True" and "False" can be summed like I mentioned. I will do some testing in a while and see....just not sure.



EDIT: My whole life has been a lie . Ok maybe that's over dramatic lol.

But I was wrong about both. Either case of True does NOT, in fact, equal 1...they apparently equal zero or nothing and can not be summed like that. Sorry about that my friend

 

[Rick Rothstein]

But I was wrong about both. Either case of True does NOT, in fact, equal 1...they apparently equal zero or nothing and can not be summed like that. Sorry about that my friend

They can be summed, but first you have to get Excel to see their numerical equivalent... you do that by involving the cell in a mathematical expression that does not change the underlying value... adding 0 will accomplish that. For the OP's range, this will return a non-zero value if one or more the the referenced cells contain a TRUE value...

=SUM(0+BA20557,0+BC20557,0+BH20557,0+BI20557,0+BL20557,0+BU20557,0+CC20557,0+CD20557,0+CF20557,0+CG20557,0+CJ20557,0+CL20557,0+CN20557,0+CR20557,0+CV20557,0+CW20557,0+DC20557)

 

[jproffer]

Thanks Rick. I thought I was losing my mind (and I'm still not SURE that I'm not )

I thought I'd used that little trick before, but I guess I forgot a step today.

 

[mcgonma]

Thanks for getting back to me! Maybe some more information will help get to the bottom of what I have and what I'm trying to accomplish.
In AX-DI, The "FALSE" value that appears in the cells is the result of another formula; it is not a hard-typed text word.
So, in AF I am trying to find in only a specific handful of cells in AX-DI that have a results of "FALSE" from the other formula. If any of the specific cells in the lager range has a calculated return of "FALSE", the I want AF to say "YES".

 

[Rick Rothstein]

This thread seems to be related to this other one by the same OP....

https://www.mrexcel.com/forum/excel...-arguments-error-post5048692.html#post5048692

 

[mcgonma]

The posts are similar. My apologies for having seemingly duplicate entries. I thought they were somewhat different so I created a new thread. Sorry about that.