# How do I write a formula in excel that lets me evaluate if each value in a row is TRUE?

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))))
``````
• This is definitely the way to go. – LPChip May 8 '14 at 17:58
• Somehow, I was under the impression that all array formulae don't work in `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
• @Jerry I know what you mean about `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.

• But if there are `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