CODEWITHSUNDEEP
excelexcel-formulacountif
Kelley Hamrick
Kelley Hamrick

Reputation: 197

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

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!

Upvotes: 0

Views: 1673

Answers (1)

Brendan
Brendan

Reputation: 4011

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.

Upvotes: 2

Related Questions