Is there a way to combine COUNTIF and FILTER?
I have some data that looks somewhat like this:
| Alice | Bob | Carla | Dave | |
|---|---|---|---|---|
| Role | Role1 | Role1 | Role2 | *Role2( |
| Jan | Activity1 | Activity2 | Activity3 | Activity1 |
| Feb | Activity2 | Activity1 | Activity1 | Activity3 |
| Mar | Activity1 | Activity3 | Activity2 | Activity2 |
I want to count the number of times someone in Role1 is doing Activity1
I can use Filter to get the matching array, and I can use COUNTIF to filter for a value in a range, but it seems that COUNTIF only supports ranges, not arrays
I also know that you could do something like =COUNT(IF(FILTER($B:$Z, $B2:$Z2="Role1")="Activity1", 1, "")) but this is giving me "Excel ran out of resources" errors for even small ranges
Is there an equivalent to COUNTIF that works on arrays or some other way to combine these?
I suspect I'm going to be forced to use a macro, but that seems like overkill