Help with UNIQUE/FILTER Formula

Question

LINK FOR FILE: https://www.dropbox.com/scl/fi/ffobzeo3jo6w4ckoltsh9/EMT-Course-Statistics-Testing.xlsm?rlkey=mu5ihza1efdr2j7mryzts8kmj&dl=0

I an still learning the UNIQUE and FILTER functions and need help applying criteria.

Each row (student) will have information across the table that pertains to that student, ie. course number, start/end dates, instructor name, etc. Because of this, I need to determine and separate completed classes from "in progress" classes so that the statistics can be performed on "completed" classes only.

The column(s) used for the criteria will look at one or more of the following beginning in row 6.

COLUMN C: Course Number

COLUMN H: EMS ACADEMY - The cells in this column will either be "Yes" (EMS Academy) or "No" (Non-EMS Academy). This will be true for classes that are also "in progress".

COLUMN K: CLASS COMPLETE - This column actually indicates if the student completed the class, not that the class itself is completed, or over. A "Yes" means student passed the class. A "No" means the student either failed or withdrew from the class, thus they did not complete the class/course. If the cell is blank, it is interpreted as being "IN PROGRESS."

NOTE: I am using INDIRECT so that the formulas will dynamically adjust as data is added for future classes. The first two formulas (Y9 & Z9) were obtained from a previous question and are correct. The value in Y3 is the last row of the table and is placed there when the command button for "List Instructors" is executed.

The following two formulas are correct and were obtained from a previous question. Y9 = 19 and Z9 = 3

Y9: =COUNTA(UNIQUE(FILTER(INDIRECT("$C$6:$C$" & $Y$3),(INDIRECT("$F$6:$F$" & $Y$3)<>""))))

Z9: =COUNTA(UNIQUE(FILTER(INDIRECT("$C$6:$C$" & $Y$3),(INDIRECT("$F$6:$F$" & $Y$3)=""))))

FORMULAS NEEDED: (the current correct answer will appear before the cell reference)

(11) Y10: Criteria is: Unique for Col C, H = "No", K = "Yes"

(8) Y11: Criteria is: Unique for Col C, H = "Yes", K = "Yes"

(2) Z10: Criteria is: Unique for Col C, H = "No", K = ""

(1) Z11: Criteria is: Unique for Col C, H = "Yes", K = ""

Thanks for you help!

Answer 1

Is this better?

https://www.dropbox.com/scl/fi/a4kzkm4qlehtlhofwz366/EMT-Course-Statistics-Testing.xlsm?rlkey=x6vlhvkj2f1wlo949anbvzt6r&dl=1

Answer 2

The syntax of FILTER is FILTER(array, include, if_empty)

array is an array of values (which can be a range) whose rows you want to filter.

include is a one-dimensional array of TRUE/FALSE values. A row of array is included in the result if the corresponding entry of include is TRUE (or any number value different from 0); it is excluded if the entry of include is FALSE or 0.

if_empty is the value to return if none of the rows of array correspond to TRUE in include.

In the formulas we're looking at, array is INDIRECT("$C$6:$C$" & $Y$3), i.e. column C of the data.

include is the product of conditions for various columns of the data. Each of the conditions returns a TRUE/FALSE array. When we multiply them, Excel treats TRUE as 1 and FALSE as 0.

TRUE*TRUE = 1

TRUE*FALSE = 0

FALSE*TRUE = 0

FALSE*FALSE=0

So the product is only 1 if both conditions are TRUE.

In the formula =COUNTA(UNIQUE(FILTER(INDIRECT("$C$6:$C$" & $Y$3),(INDIRECT("$F$6:$F$" & $Y$3)<>"")*(INDIRECT("D6:D" & $Y$3)>=$AB$8)*(INDIRECT("D6:D" & $Y$3)<=$AD$8)))), the include argument is

(INDIRECT("$F$6:$F$" & $Y$3)<>"")*(INDIRECT("D6:D" & $Y$3)>=$AB$8)*(INDIRECT("D6:D" & $Y$3)<=$AD$8)

This is the product of 3 conditions: column F (Grade) is not empty, column D (Start Date) is greater than or equal to the date in AB8, and column D is also less than or equal to the date in AD8. All three conditions must be met for a data row to be included in the FILTER result.

UNIQUE takes the result of this FILTER and weeds out the duplicates.

Answer 3

Y10:

=COUNTA(UNIQUE(FILTER(INDIRECT("$C$6:$C$" & $Y$3),(INDIRECT("$K$6:$K$" & $Y$3)="Yes")*(INDIRECT("$H$6:$H$" & $Y$3)="No"))))

Y11:

=COUNTA(UNIQUE(FILTER(INDIRECT("$C$6:$C$" & $Y$3),(INDIRECT("$K$6:$K$" & $Y$3)="Yes")*(INDIRECT("$H$6:$H$" & $Y$3)="Yes"))))

Z10:

=COUNTA(UNIQUE(FILTER(INDIRECT("$C$6:$C$" & $Y$3),(INDIRECT("$K$6:$K$" & $Y$3)="")*(INDIRECT("$H$6:$H$" & $Y$3)="No"))))

Z11:

=COUNTA(UNIQUE(FILTER(INDIRECT("$C$6:$C$" & $Y$3),(INDIRECT("$K$6:$K$" & $Y$3)="")*(INDIRECT("$H$6:$H$" & $Y$3)="Yes"))))

Answer 4

Do you want to count only courses that fall entirely within the specified period, or also courses that overlap the specified period?

Answer 5

Referencing the same workbook in the link on the original question, how hard would it be to incorporate a date filter. I would like to enter a start and end date above the statistical data in range(X14:AL36) so I could look at data within a certain timeframe.

So, using the formula you provided for Y10, how would it be implemented into the formula?

The start and end dates are listed in columns D & F on the data table.