Finding a value in select columns within a large range

mcgonma

Board Regular
Joined
Nov 2, 2011
Messages
139
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","-")
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
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 :(
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
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

Board Regular
Joined
Nov 2, 2011
Messages
139

ADVERTISEMENT

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".
 

mcgonma

Board Regular
Joined
Nov 2, 2011
Messages
139
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,619
Messages
5,523,940
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top