I want to use COUNTIFS to summarize data to a sheet from three areas of another sheet.

Berta W.

The formula below is in a sheet title: TblInstructors

The formula below is in a the above sheet title: TblInstructors (in each of the COUNT columns with different " ", @cell names, of course):

=COUNTIFS({Archive Teaches Classes 1}, AHAInstructorID@row, {ArchiveClasses Range 2}, CONTAINS("ACLS", @cell))

The other sheet being referenced is (in part):

The formula is not 'complete' as it is currently not capturing the three options for COUNTING instructor activity. The instructor may be the principle instructor (thus in the first column above, or an Assisting Instructor, or a second Assisting Instructor.

Hoping my description is enough, but not sure. Is anyone able to help?

Thank you - 'Berta W.

Jeff Reisman

@Berta W.

Your first formula counts where the instructor ID is in {Archive Teaches Classes 1}. You can add multiple COUNTIFS together in one cell to get the total count:

=COUNTIFS({Archive Teaches Classes 1}, AHAInstructorID@row, {ArchiveClasses Range 2}, CONTAINS("ACLS", @cell)) + COUNTIFS({Archive Teaches Classes 2}, AHAInstructorID@row, {ArchiveClasses Range 2}, CONTAINS("ACLS", @cell)) + COUNTIFS({Archive Teaches Classes 3}, AHAInstructorID@row, {ArchiveClasses Range 2}, CONTAINS("ACLS", @cell))

where {Archive Teaches Classes 2} references the AssistInstructor1ID column and {Archive Teaches Classes 3} references the AssistInstructor2ID column.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326463#Comment_326463

Alternatively, if you move your instructorID columns next to each other, you could create a reference to all three columns as one range. If you were doing the count on the same sheet, it would look like this:

=COUNTIFS([AHA Instructor ID]:[AssistInstructor2ID], AHAInstructorID@row)

Here it is working on my test sheet:

Berta W.

In the formula you've created, what is referenced by (ArchiveClasses Range 2)?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326468#Comment_326468

That came from YOUR formula. I assume you know what it is referencing. I figured it was the start of a class name or class type or something.

Berta W.

Oh my, Jeff I do not have a good grasp on the references used. I am thinking the first Range is the AHA Instructor ID column in the Archive Teaches Classes sheet; the second Range is the AssistInstructo1ID column in the Archive Teaches Classes sheet; the third Range is the AssistInstructor2ID column in the Archive Teaches Classes sheet. Does the COUNTIFS formula require two Ranges be designated?

=COUNTIFS({Archive Teaches Classes Range 1}, AHAInstructorID@row, CONTAINS("ACLS", @cell))+COUNTIFS({Archive Teaches Classes Range2},AHAInstructorID@row), CONTAINS("ACLS",@cell))+COUNTIFS({ArchiveTeachesClasses Range 3}, AHAInstructorID@row, CONTAINS("ACLS",@cell))

Jeff Reisman

You can use COUNTIFS with just one range and one criteria. Since you are using this on your metrics sheet to count the number of instances where that row's instructor ID was listed for a class (in any of the three instructorID columns) you really don't need the CONTAINS("ACLS", @cell))

=COUNTIFS({Archive Teaches Classes 1}, AHAInstructorID@row) + COUNTIFS({Archive Teaches Classes 2}, AHAInstructorID@row) + COUNTIFS({Archive Teaches Classes 3}, AHAInstructorID@row)

As you create your second and third COUNTIFS, just follow Smartsheet's prompts to create the references to the AssistInstructor1ID and AssistInstructor2ID columns.

Berta W.

I will try again to enter the formula in my SmartSheet. I got a return ERORR so I apparently have some syntax wrong. The reason to use the CONTAIN("ACLS",@cell) is to be able to copy that formula to three other columns in the TblInstructor Sheet when the instructor type CONTAINS either BLS, or HSFA, or PALS. Thank you