Basically, I want the most efficient way to do something like the following in the most efficient way possible and with a lot more columns:
=and(f2=true,g2=true,h2=true,i2=true)
Thank you.
Basically, I want the most efficient way to do something like the following in the most efficient way possible and with a lot more columns:
=and(f2=true,g2=true,h2=true,i2=true)
Thank you.
You can do this very concisely with an array formula. Paste the following into the formula bar and press Ctrl+Shift+Enter.
=AND(F2:I2)
EDIT:
In response to Jerry's comment, if the range you are checking will contain anything other than boolean values, you'll need to use a more specific formula (because all text, blanks, and numbers not equal to 0 will evaluate as TRUE
). The following will only consider cells with the boolean value TRUE
as true.
=AND(F2:I2=TRUE)
This should also be entered as an array function by pressing Ctrl+Shift+Enter.
If by chance you want to count both TRUE
and 1
as true, you can use this more complicated array formula (entered with Ctrl+Shift+Enter).
=AND(NOT(ISERROR(MATCH(F2:I2,{TRUE,1},0))))
AND()
. Turned out that's applicable only when there's more than one condition! +1 Still though, I prefer non-array formulae when possible.
– Jerry
May 8 '14 at 18:48
AND
not working in array formulas. It works as an aggregator (not sure if that's the proper term) like SUM
, MIN
, and SUMPRODUCT
, i.e., it can take an array as an argument, but it only returns one value, never an array of values. So, if you try to press AND
into service in the condition of an IF
in an array formula, it will not return the array of T/F values you were probably looking for, but rather a single T/F. Same goes for OR
.
– Excellll
May 8 '14 at 18:57
You could do something like this for shorter:
=COUNTIF(F2:I2,TRUE)=4
This counts the number of TRUE
and checks if there are 4.
Or if you don't want to 'count' them and put the number...
=COUNTIF(F2:I2,TRUE)=COUNTA(F2:I2)
The above will not take into consideration blank cells. If you want to count them too:
=COUNTIF(F2:I2,TRUE)=COUNTA(F2:I2)+COUNTBLANK(F2:I2)
Another option is to use the fact that TRUE evaluates to 1 and FALSE evaluates to 0 in certain circumstances.
Description of TRUE and FALSE evaluation
Using the link above you could use the function
=product(F2:I2*1)
which would evaluate to 1 if they are all true and 0 if there are any falses or blanks.
this function need to be entered by pressing ctrl+shift+enter as it's an array formula.
1
and TRUE
within the data, that formula will count the 1
as TRUE
as well, which I don't think is intended. But well, if OP's range can contain only boolean, there's no way this can go wrong.
– Jerry
May 8 '14 at 16:57