Counting across rows for ###, then countif down columns for ids matching ###

Question

I'm trying to count the number of activities each organization has done in my dataset. Right now, each row represents a single organization's list of activities.

The following formula accurately finds the number of activities per organization: =IF(COUNTA(A1:H1)=5,"yes")

However, I now need to group organizations by amount of activities (ex: how many organizations work on 2 activities, 5 activities, 7 activities...?)

I haven't figured out a way to COUNTA across a row first and then count those responses down a column. Something like this... =COUNTIF(IF(COUNTA(A1:E1)=5,"yes")="yes") from A1:E100

Any ideas are appreciated. And let me know if this doesn't make sense... my head is spinning right now from thinking too much about this!

Answer 1

By my reading, it seems as if you may be trying to do both tasks in a single formula. This may be possible with a more complex formula, but a straightforward solution is to simply add a column with activity counts to the raw data, and then count the instances of each activity count.

First, add a column to your input data with =COUNTA($A1:$H1). This will return the number of text entries for each organization. Let's say this is in Sheet1!G1:G10.

E.g.:

      [A]       [B]       [C]       [D]       [E]       [F]       [G]
[1]   Org       Act 1     Act 2     Act 3     Act 4     Act 5     Count
[2]   Org 1     Yes                 Yes                           2
[3]   Org 2     Yes       Yes       Yes        Yes                4
[4]   Org 3     Yes       Yes       Yes                           3
[5]   Org 4               Yes                                     1
[6]   Org 5               Yes       Yes                           2
...

Then create a summary table along the lines of...

      [A]                 [B]     [C]
[1]   # Activities        1       2   ...
[2]   # Organizations     *       *   ...

Where * is the formula =COUNTIF(Sheet1!$G$1:$G$10, B$1), filled to the right (note that the reference B$1 adjusts to C$1, D$1, ... to count how many organizations have the specified number of activities.