# COUNTIFS not working

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

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)
``````
• 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 Commented Nov 17, 2023 at 17:58
• No worries! Glad to help. Commented Nov 17, 2023 at 18:14

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