id="info"

Microsoft

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?

About

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.

Editor's Picks