Last week, we asked you to come up with a dynamic counting formula referencing values in a table – and there’s the one clue I offered. The data is in a table, which means table nomenclature is available, which makes writing formulas a bit easier. Thanks to Ishanahan for posting a great solution – use CountIF().

The CountIF() function in cell B2 counts the number of sales made by each person. This challenge asks you to count the number of people not meeting a quota. Ishanahan made use of the table’s nomenclature and gave us a dynamic formula in the form:


where table identifies the data, column identifies the column the values, and cell references the quota value. If you’re curious about the concatenation component, read How do I reference cells in Excel with a COUNTIF() condition?