Solution: How would you count personnel selling below quota?

Read the easy solution to last week's challenge: how to use a dynamic counting formula that references a table.

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:

=CountIf(table[column],"<"&cell)

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?

By Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.