How to count if the same student has been trained on same topic multiple times

Chuck G

Lucas Rayala

@Chuck G, it's more difficult to do this if you have multiple entries in your cell. If you were doing this for the Leasing Topics (assuming it's 1 topic per cell), then you could create a column formula that does this:

=COUNTIFS(Employee:Employee, Employee@row, [Leasing Topics]:[Leasing Topics], [Leasing Topics]@row)

Otherwise, you may need a series of formulas. Can get ugly. If you have either a short list of students or a short list of topics, you could handle this using separate columns for each student or topic. Assuming the short list was topics, you could then put your list of students in a long list, and for each topic do a countif against the student name, similar to above.

Chuck G

Yes it makes sense. The Multi-Select Dropdown column has been the one causing most trouble. I used FIND in formula to find the match but didn't know how to incorporate that into this formula.

Lucas Rayala

@Chuck G yeah, even if you used FIND to parse the multiple entries, which you can do, you still need to split those entries into different columns in order to determine what individual item is being repeated. Using FIND to parse makes sense if you have a very large list in your dropdown, or a dynamic list, BUT you only have a limited number of entries per cell. For instance, if you have 30 items in the dropdown, but an individual student will never have more than 3, then it would make sense to create helper columns that pulled the individual elements from the list and searched on them. The FIND formula needs to be modified for each column (the column name is above each formula). I believe the below works to extract the first four values, but you'll have to check.

Entry 1

=IFERROR(MID([Accounting Topics]@row, 1, FIND(CHAR(10), [Accounting Topics]@row)), "")

Entry 2

=IFERROR(MID([Accounting Topics]@row, LEN([Entry 1]@row) + 2, IF(FIND(CHAR(10), [Accounting Topics]@row, LEN([Entry 1]@row) + 2) <> 0, FIND(CHAR(10), [Accounting Topics]@row, LEN([Entry 1]@row) + 2), LEN([Accounting Topics]@row)) - LEN([Entry 1]@row) - 1), "")

Entry 3

=IFERROR(MID([Accounting Topics]@row, LEN([Entry 1]@row) + LEN([Entry 2]@row) + 3, IF(FIND(CHAR(10), [Accounting Topics]@row, LEN([Entry 1]@row) + LEN([Entry 2]@row) + 3) <> 0, FIND(CHAR(10), [Accounting Topics]@row, LEN([Entry 1]@row) + LEN([Entry 2]@row) + 3), LEN([Accounting Topics]@row)) - LEN([Entry 1]@row) - LEN([Entry 2]@row) - 2), "")

Entry 4

=IFERROR(MID([Accounting Topics]@row, LEN([Entry 1]@row) + LEN([Entry 2]@row) + LEN([Entry 3]@row) + 4, IF(FIND(CHAR(10), [Accounting Topics]@row, LEN([Entry 1]@row) + LEN([Entry 2]@row) + LEN([Entry 3]@row) + 4) <> 0, FIND(CHAR(10), [Accounting Topics]@row, LEN([Entry 1]@row) + LEN([Entry 2]@row) + LEN([Entry 3]@row) + 4), LEN([Accounting Topics]@row)) - LEN([Entry 1]@row) - LEN([Entry 2]@row) - LEN([Entry 3]@row) - 3), "")

Chuck G

Thanks so much....I'll have to give these a try.