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

enter image description here

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!

Oops, I'll change that! This is a just an example for a much larger dataset — Kelley Hamrick
It would be helpful if we could see a good sample of your data — joehanna
Just added a screenshot - I don't usually post pictures, so let me know if there's a better way to do that — Kelley Hamrick
I don't know much about pivot tables, but can definitely look into that if you think it's worth exploring! — Kelley Hamrick

Brendan Brendan · Accepted Answer · 2015-06-15T20:29:59

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.

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

1 Answers