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!
Best How To :
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
[A] [B] [C] [D] [E] [F] [G]
 Org Act 1 Act 2 Act 3 Act 4 Act 5 Count
 Org 1 Yes Yes 2
 Org 2 Yes Yes Yes Yes 4
 Org 3 Yes Yes Yes 3
 Org 4 Yes 1
 Org 5 Yes Yes 2
Then create a summary table along the lines of...
[A] [B] [C]
 # Activities 1 2 ...
 # Organizations * * ...
* 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.