Is there a way to combine COUNTIF and FILTER?

user_810713 0
Viewed 7959

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

excel-formula excel
3 Answers

I think you no need VBA. Sumproduct should work for you. I am writing from my mobile. Give a try on below formula as per screenshot.

=SUMPRODUCT((B1:E1="Role1")*(B2:E4="Activity1"))

enter image description here

user_55147470

A couple of people suggested SUMPRODUCT, which can work. It turns out that my original question also provides a solution that can work: replacing countif(filter(...)) with count(if(filter(...)))

I had tried both of those before posting this question but neither worked for me.

Why?

Turns out I was asking the wrong question. The "Excel ran out of resources" error I was getting was because I had made the assumption that Excel was smarter than it is. By using a range like $B:$ZZ I had assumed that Excel would automatically truncate to the cells actually used. That doesn't seem to be the case. When I changed my range to $B3:$Z30 then the memory errors went away and both of the solutions started working

I'm not sure what the etiquette is here. Do I mark this as the solution, mark P.b. or Harun24HR's answer as the solution, or somehow mark the question as invalid?

user_8107130

Try this: =SUMPRODUCT(--(FILTER(FILTER(A:Z,A$2:Z$2="Role1"),(A:A<>"")*(A:A<>"Role"))="Activity1")) It filters the data to only show columns with Role1 and than filters it to lose the empty data and title (even though that would not be necessary for the outcome). Then Sumproduct checks the number of occurances of "Activity1 in the remaining.

user_126342300
Related