In the summary table in F3 to I7, we have the 4 names that appear in the data log in rows, and 3 courses we want to track as column headers. Note names and courses match entries in the data log exactly. The core of the formula is the COUNTIFS function, which is configured with 2 range/criteria pairs. The first pair matches on the named range “name” (K5:K11) with criteria coming from $F4 (with column locked to allow the formula to be copied across the table). The second pair matches on the named range “course” (L5:L11) with criteria coming from G$3 (with row locked to allow the formula to be copied down the table). The COUNTIFS function counts instances of each name and course in the log, using values in the summary table. When a name and course is found, COUNTIFS returns the number 1. When a name and course is not found, COUNTIFS returns zero. We catch these results with the IF function, where COUNTIFS appears as the logical test. IF will evaluate any positive number as TRUE, and any zero result as FALSE, so we simply provide “x” for value if TRUE and an empty string ("") for value if false.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
