Count number of rows where specified value is found at least once

Question

I have a google spreadsheet where I am trying to count the number of rows where a certain value is present in at least one column. The number of columns with data varies by row.

For example, let's use the following sheet as an example:

https://docs.google.com/spreadsheets/d/1yUnYBsmjKIOF_PubYQ6G41fmIPcw_7hzkiQ6qMIoN64/edit?usp=sharing

Each row represents a task, and the data for who worked on the project is added by adding additional columns.

I would like count how many tasks each person has worked on at least once. (If Person A worked on Task A multiple times, it would only count as 1).

I've tried using formulas such as COUNTIFS or COUNTUNIQUEIFS, but am being thrown off by the fact that the number of columns can vary.

Any ideas of how I can accomplish this?

Answer 1

See if this helps

=countif(ArrayFormula(mmult(N(Sheet1!E2:100=A1), transpose(column(Sheet1!E2:2)^0))), ">1")

Answer 2

See the added sheet "Erik Help," cell A1, for the following array formula:

=ArrayFormula(QUERY({{"Employee";UNIQUE(QUERY({Sheet1!E2:E;Sheet1!H2:H;Sheet1!K2:K;Sheet1!N2:N;Sheet1!Q2:Q;Sheet1!T2:T;Sheet1!W2:W;Sheet1!Z2:Z;Sheet1!AC2:AC;Sheet1!AF2:AF},"Select * Where Col1 Is Not Null",0))},{"Projects";COUNTIF({Sheet1!E2:E&Sheet1!H2:H&Sheet1!K2:K&Sheet1!N2:N&Sheet1!Q2:Q&Sheet1!T2:T&Sheet1!W2:W&Sheet1!Z2:Z&Sheet1!AC2:AC&Sheet1!AF2:AF},"*"&UNIQUE(QUERY({Sheet1!E2:E;Sheet1!H2:H;Sheet1!K2:K;Sheet1!N2:N;Sheet1!Q2:Q;Sheet1!T2:T;Sheet1!W2:W;Sheet1!Z2:Z;Sheet1!AC2:AC;Sheet1!AF2:AF},"Select * Where Col1 Is Not Null",0))&"*")}},"Select * Order By Col2 Desc"))

I will break it down somewhat here, and then encourage you to further dissect it if deeper learning is required.

Obviously, it's an array formula. In this case, that means that one formula is producing the entire report.

The outermost QUERY is just putting the results inside the double curly brackets {{ }} in order by project count: QUERY( {{...}} ,"Select * Order By Col2 Desc")

You see those double curly brackets. But really, it's an outer set of curly brackets containing two more sets of curly brackets: { {...},{...} } The inner two arrays create the first column and second column of the report, respectively. The comma means to place them side by side. You will notice that the first element of each of those inner arrays produces the header for the respective columns (i.e., "Employee" and "Projects").

The semicolon following each of these headers means to place what follows underneath rather than side by side. You will notice a lot of those semicolons in the first inner array. Because you said that there would never be any more than 10 people working on any one project, we can predetermine all columns that might hold names and virtually "stack" them with those semicolons, forming one long virtual column. Of course, many of those columns will be empty, because many projects won't have a full ten names of people attributed to them. So this virtual column of names is wrapped in its own QUERY that will weed out nulls:

QUERY({Sheet1!E2:E;Sheet1!H2:H;Sheet1!K2:K;Sheet1!N2:N;Sheet1!Q2:Q;Sheet1!T2:T;Sheet1!W2:W;Sheet1!Z2:Z;Sheet1!AC2:AC;Sheet1!AF2:AF},"Select * Where Col1 Is Not Null",0)

To this, I applied UNIQUE, which provides the first-column list of unique names (rather than every time a name appears):

UNIQUE(QUERY({Sheet1!E2:E;Sheet1!H2:H;Sheet1!K2:K;Sheet1!N2:N;Sheet1!Q2:Q;Sheet1!T2:T;Sheet1!W2:W;Sheet1!Z2:Z;Sheet1!AC2:AC;Sheet1!AF2:AF},"Select * Where Col1 Is Not Null",0))

So the complete first inner virtual array (which forms the complete first column of the final report) looks like this:

{"Employee";UNIQUE(QUERY({Sheet1!E2:E;Sheet1!H2:H;Sheet1!K2:K;Sheet1!N2:N;Sheet1!Q2:Q;Sheet1!T2:T;Sheet1!W2:W;Sheet1!Z2:Z;Sheet1!AC2:AC;Sheet1!AF2:AF},"Select * Where Col1 Is Not Null",0))}

The second inner virtual array uses the ampersand to join all names assigned to each project into one long string. So, for instance, if Chris, John and Ryan all worked on a project (and some of them multiple times), their rows (unseen) concatenation might look like this: ChrisChrisJohnChrisRyanChris.

We run a COUNTIF on each of this virtual array made up of such concatenations, and the condition you'll see is made up largely of the entire UNIQUE clause from the first inner virtual array (which is the shortlist of all names possible). You will notice that this is appended front and back by asterisks like this: ""&UNIQUE(...)&"" Asterisks are wildcards for any number of characters. So essentially this will search those long concatenated strings for the appearance of each name anywhere; and as soon as a name is found, COUNTIF registers it as TRUE... once (not each time it appears in the string).

So that second inner virtual array looks like this in isolation:

{"Projects";COUNTIF({Sheet1!E2:E&Sheet1!H2:H&Sheet1!K2:K&Sheet1!N2:N&Sheet1!Q2:Q&Sheet1!T2:T&Sheet1!W2:W&Sheet1!Z2:Z&Sheet1!AC2:AC&Sheet1!AF2:AF},"*"&UNIQUE(QUERY({Sheet1!E2:E;Sheet1!H2:H;Sheet1!K2:K;Sheet1!N2:N;Sheet1!Q2:Q;Sheet1!T2:T;Sheet1!W2:W;Sheet1!Z2:Z;Sheet1!AC2:AC;Sheet1!AF2:AF},"Select * Where Col1 Is Not Null",0))&"*")}

Without that outermost QUERY I mentioned up front here, you'd still get accurate results; they'd just be displayed in whatever order the UNIQUE names list happened to appear in the projects. I felt it would make more send to order them by whoever had the most projects to the least.