When do I use quotation marks in an Excel formula?

[Deb]

What are the guidelines as to when you do and do not use quotation marks in
the criteria section of a function.

 

[David Biddulph]

Quotation marks around a text string; no quotation marks around a number.

 

[T. Valko]

It can get confusing in some cases but the GENERAL rule of thumb is:

Always quote text
Never quote numbers

Now, here's a few examples of where it can get confusing!

=COUNTIF(A:A,">0")
=COUNTIF(A:A,">"&B1)

Both COUNTIF and SUMIF evaluate TEXT numbers and numeric numbers as being
equal so both of these will work:

=COUNTIF(A:A,"0")
=COUNTIF(A:A,0)

=SUMPRODUCT(--(A1:A10=--"1/1/2009"))

In this case we're using a TEXT string to represent the date Jan 1 2009.
But, we're coercing that text string into a number value by using the double
unary: --.

If you just used:

=SUMPRODUCT(--(A1:A10="1/1/2009"))

Then the criteria is being evaluated as the literal TEXT string 1/1/2009 and
not the date 1/1/2009.

If you just used:

=SUMPRODUCT(--(A1:A10=1/1/2009))

Then the criteria is being evaluated as the math expression: 1 divided by 1
divided by 2009

So, there are many nuances to learn but if you follow the GENERAL rule of
thumb you'll be OK!

 

[Sean Timmons]

Use quotes when directly referencing a text value. If you are referencing a
cell (A2), you would use quotes only if using the INDIRECT() function.
Numeric values would not need quotes unless they are stored as text.