Count Instances Across Sheets

Art Schneiderheinze

Part 1. How Many Rows

Is there an easy way to count how many times a value in one sheet appears in a column of another sheet?

Sheet #1

A cell in a column that is being checked may look like this:

Project #1 | ABS 100,ABS 102,CAR 144,ESP 332

Project #2 | ABS 102,SPE 344,SPE 422,STEM 300

Project #3 | AAY 320,ABS 102,SPE 344

In Sheet #2, I'd like to display how many rows that value appears. For example, if I enter "ABS 102" in Sheet #2, it would look at the column that includes the above three sample rows, and display "3", since ABS 102 appears in all three rows. If I enter "SPE 344", it would display "2".

Part 2. Which Projects

Related to this, in a different cell in Sheet #2, could it COLLECT the the programs in Sheet #1 that include that value? So, if I enter "ABS 102" in Sheet #2, it displays "Project #1, Project #2, Project #3" since it appears in all three rows in Sheet #1? For "SPE 344", it would display "Project #2, Project #3" since it only appears in those two rows?

Genevieve P.

Hi @Art Schneiderheinze

The answer to both your questions is Yes!

1) Count Value Cross-Sheet

It looks like this may be a multi-select column with all the values in Sheet 1, is that correct? If so, we can use a HAS function within a COUNTIF to see if the cell has that specific value you input on Sheet 2.

Try something like this:

=COUNTIF({Column with Values in Sheet 1}, HAS(@cell, [Value Sheet 2]@row)

Your sheet 2 would look like this:

Then you can type something different in the "Value Sheet 2" column and it will auto-calculate how many times this new value appears.

2) Collecting Programs for this Value

Similarly, next to the COUNTIF formula we could use a JOIN(COLLECT to do the exact same thing, but instead of just counting it will bring back the values in the Program column:

=JOIN(COLLECT({Program Column}, {Value Column Sheet 1}, HAS(@cell, [Value Sheet 2]@row)), " / ")

You can change up the symbol to separate the values by adjusting what appears in the quotes at the end of the formula.

See: Cross-sheet formulas

Let me know if you get stuck at any point building these out!

Cheers,

Genevieve

Art Schneiderheinze

Thanks!!! I actually ended up having to use CONTAINS rather than HAS because the cells in Sheet 1 had multiple course codes (e.g., ABS 100,ABS 102,ABS 103 and not just ABS 100). As a result, with HAS I was getting no results when looking for instances of, say, ABS 102. With CONTAINS, it gave me the correct result because it didn't rely on exact matches.

Otherwise, thanks so much!!!!

Genevieve P.

No problem at all! I'm glad you found a function that works. 😊

Art Schneiderheinze

Is there an easy way to COLLECT two things from Sheet 1 at the same time? In addition to the PROGRAM, I want to college the PROGRAM TYPE (i.e., is it a required course, elective, concentration, etc.). The PROGRAM TYPE is another column in Sheet 1:

| PROGRAM | PROGRAM TYPE | COURSES
| Applied Behavioral Sciences | R | ABS 100,ABS 102,ABS 105,CMR 233

In Sheet 2, I'd like it to collect the program AND the Project Type.

Or, if I collected them in separate columns in Sheet 2, it would put the programs in the first column and project type in second column:

Applied Behavior Sciences,Design Thinking,Career
R,C,R

Then, how could I parse this so in the subsequent rows, I can list each program and its project type from these two collections in separate columns on separate rows:

Applied Behavior Sciences | R (I can make this show "Required" rather than "R"
Design Thinking | Concentration
Career | Required

Genevieve P.

The easier thing to do might be to have a helper column in Sheet 1, which is a simply + formula to bring together both the Program Name and the Program Type into the same cell:

=[Program Name]@row + " - " + [Program Type]@row

Then you can HIDE this column in the main sheet, and use this Helper Column as the column to return in your JOIN(COLLECT formula. This will keep the Type next to the Name.

=JOIN(COLLECT({Helper Column}, {Value Column Sheet 1}, HAS(@cell, [Value Sheet 2]@row)), " / ")

For an output of:

Program 1 - R / Program 2 - E

You could have a second column that uses the JOIN(COLLECT and looks only at the Program Type, but then you wouldn't know if the E belongs to Program 1 or 2 as it would be in a separate cell with the R. Doe that make sense?

In regards to parsing out the data, the JOIN(COLLECT formula can only bring data into a cell. There isn't a way to automatically parse this out down below into new rows.

If this is the preferred way you want to see the data, I would actually recommend creating a Report off of the source sheet instead. You can GROUP by specific information in a Report, such as if the Course Code "Contains" a specific value. Would this be better for your purposes?