Counting Instances from Sheet 2 Based on Criteria

Art Schneiderheinze

In Sheet 1, I want to report the number of instances of a string, based on two criterion : (1) BL or OL and (2) Development Term.

SHEET 2
COURSE | TERM CODE | MODALITY | SOURCE TYPE | STUDENTS | COMBINED  
ENG 101 | 201910 | BL | M | 25 | ENG 101$201910@BL^M~24 
ENG 101 | 201930 | BL | M | 18 | ENG 101$201930@BL^M~18 
ENG 101 | 201990 | OL | C | 16 | ENG 101$201990@OL^C~16
GEN 202 | 201910 | BL | M | 11 | GEN 202$201910@BL^M~11

I added a cell (Combined), which is a combination of the previous four columns (separated by unique characters to help perhaps with a FIND) to hopefully simplify the reference and reduce the number of cells that have to be referenced (there are nearly 120,000 rows).

In Sheet 1, for example, if want to show that, for example, there have been 6 sections of ENG 101 since 201960 (this is a term code)

SHEET 1
COURSE | DEV TERM | NUM SECTIONS (BL) | NUM SECTIONS (OL) | AVG NUM STUDENTS (BL) | AVG NUM STUDENTS (OL)
ENG 101 | 201960 | 6 | 4 | 18 | 12

I know I could use something like INDEX-CONTAINS to count the number of instances of a particular course, either in the COURSE or COMBINED columns of Sheet 2. However, I want to ONLY count instances that not only contain the COURSE (e.g., ENG 101), but also have a certain modality (BL or OL), and were in a term equal to greater than the DEV TERM in Sheet 1.

So, it may count 40 instances of ENG 101 in total, but there may have only been 6 on the schedule since (and including) a particular DEV TERM (201960) and in BL modality.

I also want to find the average of the number of students in those 6 sections. (I guess I could use the total number of students across the sections that meet that particular criteria of modality.)

Kelly Moore

Hey @Art Schneiderheinze

I need clarification please to understand your problem. I understand you have columns of data and you want a count using some of the data. You have also combined your columns into a string?

Have you tried COUNTIFS against your original column data? If yes, what did that formula yield?

On Sheet 1, the sheet where the formula resides, does the Course Name exist? For the moment, I will assume it does

For the Development Term on Sheet 1, how is that being entered? We could gather that with a formula.

For the column

NUM SECTIONS (BL)

=COUNTIFS({Sheet 2 Course Name}, [Course Name]@row, {Sheet 2 Modality}, "BL", {Sheet 2 Dev Term}, @cell>=[Dev Term]@row)

 NUM SECTIONS (OL)

=COUNTIFS({Sheet 2 Course Name}, [Course Name]@row, {Sheet 2 Modality}, "0L", {Sheet 2 Dev Term}, @cell>=[Dev Term]@row)

AVG NUM STUDENTS (BL) 

=AVG(COLLECT({Sheet 2 Students}, {Sheet 2 Course Name}, [Course Name]@row, {Sheet 2 Modality}, "BL", {Sheet 2 Dev Term}, @cell>=[Dev Term]@row))

Would these formulas work for you?

Kelly

Art Schneiderheinze

It's perfect! The course and DEV TERM on sheet 1 is entered manually. Now, I need to figure out how to handle the data set better. With over 20,000 rows (sections offered every term for 2 years), plus the other references I make on Sheet 1, I cannot include the average students because I exceed the max number of references.

This was my hope of using the combined string in Sheet 2 (ENG 101$201910@BL^M~24). Maybe it would help referencing JUST that column, Can you include FINDs in the formula:

=COUNTIFS(FIND??? {Sheet 2 Course Name}, [Course Name]@row, FIND ??? {Sheet 2 Modality}, "BL", FIND??? {Sheet 2 Dev Term}, @cell>=[Dev Term]@row)

Or if the combined string CONTAINS the values in Sheet 1, when it references Sheet 2?

Kelly Moore

Hey Art

I've been playing with your data trying to reduce the terms within the COUNTIFS or COLLECT using the text string only. The opportunity comes in trying to extract numbers. Once we put a number inside of a text string, it becomes text. It might look like a number as it is parsed out, but it behaves as text. That means one can't use a greater than or equal with it, or compute averages on it. The VALUE function does not work within a COUNTIFS or COLLECT function- or I've never been able to make it work. So I can easily find the Course name and the Modality in your textstring using CONTAINS but I must pull the fields with number values directly from their original columns. This isn't about using FIND vs CONTAINS, it's about converting text to VALUE().

What are you doing with the data? If you are pulling it into a dashboard you may be able to use Summary Fields, dependent upon your licensing plan. Summary fields would eliminate cross sheet formulas.

I also wondered if you needed to keep all of the data on one sheet? Consider if two years of data is necessary. I have one set of data - only 7500-8000 rows but more than 200 columns wide. I maxed out the number of allowed calculations on one sheet. I reduced the number of calculations by segregating the data into three sheets based on a categorization of the data- for example, all of your English courses go to one sheet, General goes to another, etc. Whatever makes sense. I have a single intake sheet then I have multiple Move Row Automations that move the rows to different sheets, based on criteria, as the rows are entered. I later pull all the data back together, as needed, in reports. This may or may not work for you - in your case you would need to utilize the Summary fields in these reports. If you go this route make sure these column fields are named exactly the same sheet to sheet.

Here's the best I could do with your formulas. Feel free to mark the post as Unresolved to turn it back over to the community.

=AVG(COLLECT({Sheet 2 Students}, {Sheet 2 Combined}, AND(CONTAINS([Course Name]@row, @cell), CONTAINS("BL", @cell)), {Sheet 2 Dev Term}, @cell>=[Dev Term]@row))

Kelly

Art Schneiderheinze

I had a thought, Kelly. If I focus just on the combined string ENG 101$201910@BL^M~24, I could easily do the counts (referencing 20,000 cells, versus 20,000 x 4 separate columns).

For the enrollment (the number at the end, following the ~), since I can't sum or average those numbers (for instances that appear after a certain term (e.g., looking at 201910 in the sample combined string), could I COLLECT and JOIN all of the enrollments for rows that meet the  {Sheet 2 Dev Term}, @cell>=[Dev Term]@row) criterion?

Then, in Smartsheet, I could extract the enrollments in that comma-separate value, to either sum, average, or whatever with?

EXAMPLE:

CCD 699B$201910@BL^C~9
CIC 501$201910@OL^C~22
FND 510$201960@OL^C~26
LDR 521$201960@OL^M~14
SPY 518$202030@BL^I~4

Let's say the above rows (in Sheet 2) meet the criterion (DevTerm in greater than 201890, the part after the $ sign). I can count # of sections easily, as you provided. Thanks!

Would I be able to COLLECT and JOIN the part after the ~ symbol, with a comma in Sheet 1:

9,22,26,14,4

Then, in another cell, have some formula that sums all of the numbers in this comma-separated list? I don't anticipate this comma-separated list having more 200 numbers (e.g., 15 sections each term, 4 terms a year, 3 years since meeting the criterion for the DevTerm), so I shouldn't have a problem with the maximum string length.

Kelly Moore

I'll work on that and see.

Have you tried using reports to see if you can gather the data that way straight from your sheet? Do you have access to the Pivot App?

Kelly Moore

Hey Art

It was, unfortunately, as I thought. Yes, you can take your combined text strings and join them into one very long text string that has commas in it. That's how smartsheet is reading that cell. It doesn't see it as a comma separated variable field. You cannot separate it

An idea:

Could you make some new sheets, say one per course, that you could copy rows over to. This would be done easily with automation - it would be hands free. Leave originals on your main sheet but have some behind the scenes sheets to copy to. This would segregate your data (no more 20,000 rows on a single sheet) and allow you to do calculations. Since, when you copy rows, you only copy values and not formulas, you would not be restricted by any previous number of calculations done on the main sheet. You would be starting over from zero calculations on these new sheets. Then, if your Sheet 1 is your metrics sheet, you could pull the data from these other sheets. Then you wouldn't have to keep trying to wrestle with this combined text string that, unfortunately, isn't getting us anywhere.

If you are unfamiliar with setting up copy row automation, here's some info.

https://help.smartsheet.com/articles/2479626-automatically-move-or-copy-rows-between-sheets

Kelly