COUNTIFS not working

Question

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

Answer 1

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:

=countif({D8:D16;H8:H16;L8:L16;P8:P16;T8:T16},true)

Answer 2

Use SUM and FILTER

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

Formula

=SUM(FILTER(D8:T16*1, NOT(MOD(
   SEQUENCE(1,COLUMNS(D8:T16),0),4))))

Explanation

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
      {0,1,2,3,4,5,6,7,8,9,10,11,12,...}
      
      # after MOD
      {0,1,2,3,0,1,2,3,0,1,2,3,0,...}
      

   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.