How to Write an Excel Criteria Statement That Links to a Specific Cell

by Matthew Kuo on November 30, 2013

in Formula Writing

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

I generally give Excel a lot of credit for being an intuitive and easy to learn tool.  However, writing criteria statements that link to cells is probably one of the most un-intuitive aspects of formula writing in Excel.  This is a very common mistake that occurs and therefore, should always be error-checked whenever you are writing criteria statements.

Writing Excel Formula Criteria

Most people understand the basic rules around writing an Excel criteria statement.  Whenever you start a complex criteria statement (meaning ones that involve mathematical operators or text), you have to remember to add the quotation symbols around whatever you write.

For example, if we wanted to count just the values that were greater than 0 within a given range, we would write:

= COUNTIF ( Range, “>0” )

Criteria1

In situations where you want to the criteria exactly equal to either a number or a cell, the quotation marks are no longer necessary.  We can just list the number that we want in the criteria section of the syntax.

In this example, I just want to count the values that are equal to 0 within a given range:

= COUNTIF ( Range, 0 )

Criteria2

And in this last example, I just want to count the values that are equal to the value in cell F3.  It’s important to note that when writing this last statement, instead of typing the text “F3”, I can just directly click on the cell I want to get it into the formula:

= COUNTIF ( Range, F3 )

Criteria3

The Common Mistake

The common mistake arises when we try to bring these components together – when we want to write a complex criteria statement that refers directly to a cell.  To continue along with the previous examples, let’s say we want to count all values in a given range that are greater than the value in cell F3.

To complete this task, many of us would just write:

= COUNTIF ( Range, “>F3” )

Criteria4

If you audit this formula, you’ll soon realize that your intended criteria statement has not registered.  Had it worked correctly in the example above, the return value would be 3.  The problem is that Excel just reads the entire entry between the quotation marks as text.  Therefore, you’ve essentially told Excel to only count values that are equal to the text “>F3” which obviously won’t match any of your values.  Since no values match these conditions, the statement returns a zero value.

The Proper Syntax

The correct way to write the criteria statement is as follows:

“Mathematical Operators”    &    Cell Reference

In terms of individual steps, you would perform the following:

  1. Open quotation mark
  2. Insert mathematical operators
  3. Close quotation mark
  4. Insert the ampersand symbol
  5. Click on the cell you want to refer to

The key to not making a mistake is that you MUST NOT put quotation marks around your cell reference.  You also MUST INCLUDE an ampersand symbol.  If you’ve written the statement properly, it will look like this:

= COUNTIF ( Range,  ”>”&F3)

Criteria5

How to Ensure that it’s Working

The correct way of writing the criteria statement looks weird, and thus, makes us prone to making a mistake.  While this proper syntax can be difficult to remember (which is probably why you’re reading this page in the first place) there is one trick to ensure that your criteria statement is working:

Make sure your cell reference is highlighted in color in the formula bar

If the text is colored when you click inside the formula bar, you know that the cell is being referenced.

Criteria6

Having an error in this situation is dangerous because Excel does NOT return an error value after you’ve made the mistake.  Therefore, it’s easy to pass this over even though the calculation wrong.  Always go back and double check your criteria statements with the trick above to ensure good quality control.

{ 3 comments… read them below or add one }

Phil Teng January 18, 2015 at 10:03 am

Some of best advice on the web. Thanks !

Reply

jihunlee October 14, 2015 at 7:25 am

thank you so much ^^

Reply

Gene Graves September 18, 2016 at 5:39 pm

I kept coming up with the #div/0 problem. Your explanation of the use of the ampersand solved all the problems. I searched around the net a lot and was getting quite frustrated. Thank you.

Reply

Leave a Comment

Previous post:

Next post:


Related pages


sample excel inventory spreadsheetsexcel delete double entrieshow to lock excel formulashow to title a graph in excelif statements in excel 2010excel sheet to pdf converter onlineuniversal studios tickets uclastacked and clustered chart excelexcel remove duplicates based on two columnshow to use hlookup formula in excelnum error in excelexcel remove duplicate rowshow to delete duplicate data in excelsumif and vlookupexcel sort formula resultsnested vlookup in if statementexcel delete duplicates in columnroundup to nearest 1000 in excelhow to write an excel formulatask scheduler excel templateexcel isna vlookupucla grading policydelete multiple rows in exceliserror formula with vlookupif statement in excel formulaexcel checkbook templateexcel function hlookupcreate bins in excelhlookup excel 2010formula for vlookup in excelvlookup comparehistogram generatortick yes symbolvlookupsgoogle spreadsheet hlookupvlookup on multiple criteriaexamples of checklists in excelvlookup matchhow to use hlookupsumproduct not workinghow can i find duplicates in excelhow to draw a horizontal line in excel graphvlookup excel functionhow to create a vlookup in excelhow to create vlookup formulawhat is hlookup and vlookupbuilding macros in excelcreate a excel macroexcel matching formulajohn isnacool excel designsnested if excel formularound to nearest thousand exceldelete even rows in excelvlookup in excel 2010 exampleexcel mean deviationmicrosoft word 2010 free trial downloadhow to use iferror function with vlookup in excel 2010count vlookupassignment notebook templatehow to build an excel spreadsheetexcel double lookupeliminate duplicates in excelexcel clustered stacked columnhow to histogram excelpower query for excelvlookup in excel 2003 with examplecreate histogram in excel 2013purpose of vlookup in excelinsert statement in excelexcel delete double entriesindirect excel function exampleuniform distribution histogramexcel formula tutorialformulas used in excel 2007excel remove duplicate valuesexcel table lookuphow to build excel macroshlookup excel 2010 examplegenerate lottery numbers in excelapproximate matchexcel how to delete duplicatesnested countif statements
\n