google sheets countifs based on true

Anyone able to tell me why this generates 0 and not 5 please? My COUNTIF works fine below, but selecting multiple criteria doesn't seem to... Probably some simple syntax error..?

2 Answers 2


0 is correct because with each new criteria_range you are ANDing the results (i.e. your formula as shown is equivalent to 'give me the count where there is a TRUE present in the same position in each column').

If you want to return 5, that implies to me that you actually want 'give me the count of TRUEs across all rows', in which case you should stack the columns into one using array literals:

  • God of biscuits indeed - thank you so much; I'm not au fait with arrays but indeed it solves my problem; I had thought about it 'and-ing' just couldn't get around it! <3
    – Stu Bridge
    Nov 17 at 17:58
  • No worries! Glad to help. Nov 17 at 18:14


This aporoach uses a single range and is easy to reuse and manage. It leverages the consistent column pattern found in your sample data.




  1. FILTER is applied to the range D8:T16 to remove columns that aren't wanted.
  2. All the values in the range D8:T16 are multiplied by 1 so Sheets will coerce the checkbox values from TRUE and FALSE to 1 and 0.
  3. The FILTER's condition argument includes only every fourth column starting with the first. This is achieved by:
    1. Creating a one row SEQUENCE of numbers based on the number of columns in the range, starting at 0 and incrementing by 1
    2. Wrapping the SEQUENCE in MOD using 4 as the divisor, which changes the number SEQUENCE:
      # before MOD
      # after MOD
    3. MOD is then wrapped in NOT to convert the values 0,1,2,3 to TRUE,FALSE,FALSE,FALSE. Now the position of all TRUE values in the FILTER condition matches the position of the columns that should be counted.
  4. Lastly, SUM is used to total all values from the array of ones and zeros returned by the FILTER. This is equivalent to counting.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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