I'm trying to get an array into the criteria of a COUNTIFS function.

Here is a sample of the data:

Name    Type    Location    Progress

Name_1  Type_1  Location_1  Stage_1

Name_2  Type_1  Location_3  Stage_2

Name_3  Type_2  Location_1  Stage_4

Name_4  Type_3  Location_2  Stage_4

I'd like a COUNT of how many rows are of a particular 2, and fall within a set of preferred locations and preferred stages.

In this example, the preferred locations are Location_1 and Location_2 and the preferred stages are Stage_3 and Stage_4.

Currently, I'm having to use multiple lines:

=COUNTIFS(B:B,"Type_2",C:C,"Location_1",D:D,"Stage_3") +COUNTIFS(B:B,"Type_2",C:C,"Location_1",D:D,"Stage_4") +COUNTIFS(B:B,"Type_2",C:C,"Location_2",D:D,"Stage_3") +COUNTIFS(B:B,"Type_2",C:C,"Location_2",D:D,"Stage_4")

This would return an answer of 1 which would be from Name_3.

The aim is to have in another sheet, a table with 2 headers; PreferredLocations and PreferredStages, listing below the relevant info.

I'm hoping you'll be able to help in condensing the above function down into a more dynamic function in a single line:


But the above doesn't work because of the Named Ranges 'PreferredLocations' and 'PreferredStages'. Using Cell Ranges instead of Names Ranges encounters the same error.

The closest similar question I've found was at https://www.mrexcel.com/forum/excel-questions/984698-countifs-function-criteria-cell-reference-range.html but I couldn't get it to work for me.

I suspect the answer will be some sort of SUMPRODUCT or QUERY.

2 Answers 2


Assuming your data is in N2:Q5, and the list of acceptable Stages in S2:S3, the formula below will return what you want.

=rows(filter(N2:Q5, not(isna(match(Q2:Q5, S2:S3, 0)))))

enter image description here


I'm not convinced @ttarchala's A provides for the "of a particular 2" requirement (which is hardly surprising as somewhat ambiguous, IMO) so would suggest a QUERY alternative such as:

=counta(query(A:D,"select A where B='Type_2'and ((C='Location_1' OR C='Location_2') and (D='Stage_3' OR D='Stage_4')) "))

However I'd recommend checking this does achieve what you want (I presume you can adjust it quite easily if not).

Your Answer

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

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