Takeaway: Use Excel's flexible COUNTIF() function to count entries based on a condition that includes a cell reference.
Counting values is an easy task using Excel's COUNTIF() function. This function counts the number of cells in a range that meet a specific condition. You supply the range and a condition, which can be a literal value or a expression. Today, we'll combine the two—sort of. Let's suppose you want to know the number of people who surpassed last year's top commission in the spreadsheet below.
In this case, the range is the commission totals in cells B7:E7. The condition is any value that's greater than last year's top commission. Let's say that value is $12,500. In Excel language, that's >12500. Now, let's transfer all that to the actual sheet. As you can see below, the COUNTIF() functions (B11 and B12) return 1 and 3
Be sure to wrap the conditional expression in quotation marks. Only one salesperson surpassed last year's top commission; three were equal to or less than last year's top commission.
As is, it works fine, but it could be better. Let's suppose you're using the same sheet this year. Next year, you'll want to compare the commissions to this year's top commission of $14,496. That means you'll have to update the COUNTIF() functions or refer to cell B10 in the condition itself. That way updating just the top commission value in cell B10 gets the job done - you won't have to update the actual functions.
To create a more dynamic function, concatenate the cell reference as follows:
=COUNTIF(B7:E7,">" & B10)
=COUNTIF(B7:E7,"<=" & B10)
Next year, you simply write over the current top commission value with 2011's top commission value and both functions will update automatically. Of course, this is a simple example with a simple expression, but knowing how to write a conditional expression that references a value in the sheet opens a lot of possibilities for Excel's COUNTIF() function.