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