Enterprise Software optimize

How do I reference cells in Excel with a COUNTIF() condition?

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

=COUNTIF(B7:E7,">12500")
=COUNTIF(B7:E7,"<=12500")

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.

TechRepublic's Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!
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.

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.

5 comments
stapleb
stapleb

Thanks again Susan. I think Countif is an extremely useful function and often show people how to use it, including occurrences of text, either complete, or using wildcards for the criteria. With Excel 2007 you now have the Countifs which is even more powerful.

RU7
RU7

I would change the formulas to: =COUNTIF(B7:E7,">" & MAX(B10:E10) and =COUNTIF(B7:E7,"

lee_dimambro
lee_dimambro

Col A Col B Formula in Col B Sales 3 =+COUNT(A1:A6) 100 5 =+COUNTA(A1:A6) 200 1 =+COUNTBLANK(A1:A6) 6 =+COUNTIF(A1:A6,"zzzzzz") 300 1 =+COUNTIF(A1:A6,"=300") cogs 2 =+COUNTIF(A1:A6,"=300")+COUNTIF(A1:A6,"=200") 400 2 =+COUNTIF(A1:A6,"*s*") 50 a 1 =+COUNTIF(A1:A6,"*" & B8 & "*") 1000 500 2 =+COUNTIF(A1:A17,">=" & B9) 500 2 =+COUNT(A1:A17)-COUNTIF(A1:A17,"=" & B9) 30 1 =COUNTIF($A$1:$A$17,"????") TRUE 2 =COUNTIF($A$1:$A$17,???*???) #DIV/0! 2 {=SUM(IF(ISTEXT(DATA),1))} #N/A 15 {=SUM(IF(ISNONTEXT(DATA),1))} 1 {=SUM(IF(ISLOGICAL(DATA),1))} 2 {=SUM(IF(ISERROR(DATA),1))} 1 {=SUM(IF(ISNA(DATA),1))} 1 =COUNTIF(DATA,"#DIV/0!") 1 =COUNTIF(DATA,">100")-COUNTIF(DATA,">200") 8 =+COUNTIF(DATA,">0")

Gordon Or-8
Gordon Or-8

Thanks for that Top tip. (I assume it will also work with other formlae requiring criteria too?)

evans
evans

Very useful tip, i sent this to some of my not so experienced staff and they appreciated it. Thanks