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:


Thank you.


You can do this very concisely with an array formula. Paste the following into the formula bar and press Ctrl+Shift+Enter.


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.


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

  • 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:


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


The above will not take into consideration blank cells. If you want to count them too:


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


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.