COUNTIF with multiple criteria and list

Question

I need to count the number of cells in which 'CRITERIA 1' is satisfied, 'CRITERIA 2' is satisfied and 'CRITERIA 3' falls within the set of values contained in column E.

I am currently using the following formula:

=SUM(COUNTIFS(A2:A11,"TRUE",B2:B11,"TRUE",C2:C11,{"2","4","6","9","10"}))

But in my real table, the list of data within 'CRITERIA 3' is longer and more complicated and I would prefer to reference the cells in column E rather than the specific data, i.e. something like:

=SUM(COUNTIFS(A2:A11,"TRUE",B2:B11,"TRUE",C2:C11,{"E2:E6"}))

Please note that the data contained in this example is different to the data in my real table. The real table is considerably longer and more complex than this table.

Any suggestions?

Answer 1

Decided to put my comment as an answer so I can show a picture that it works:

You are close. The Range is an array so no need for the {""} wrapper

Just use:

=SUM(COUNTIFS(A2:A11,"TRUE",B2:B11,"TRUE",C2:C11,E2:E6))

This is an array formula and must be confirmed with Ctrl-Shift-Enter.