Excel 2007 : quotes and ampersand in Count formula

**adamNBU** · 02-24-2010, 05:51 AM

Thank you for reading this post.

I am using a countifs formula using dates where C363 is today() and I am counting all instances in column M that match B5 and are BEFORE today's date. Column C lists dates in chronological order for 2010.

This formula works fine:

=COUNTIFS(C2:C358,"<="&C363,M2:M358,B5)

My questions are,
1) why do I need quotes around "<=" and
2) why do I need an ampersand (&) before C363 which contains today() ??

I'm not asking to explain the logic behind Excel but rather, when is it necessary to use quotes and what does the ampersand actually do?

I frequently make the error of writing =COUNTIFS(C2:C358,<=C363,M2:M358,B5) and after about 15 minutes, I remember the quotes and ampersand.

Thank you again.
Adam

**teylyn** · 02-24-2010, 05:58 AM

Hi Adam,

I personally hate this syntax and find it confusing as ..., but I guess the reasoning behind it is that you need to construct a text string for the argument. If you only want to compare what's in C363, then you can just put C363 as the parameter, but if you go any fancier than a simple "equal to", you'll have to start the "" and &

Say if C363 has a number, 333, then

"<="&C363

will create
"<=333"

You can enter that directly as a parameter, too, but you have to include the "".

So, in short, the "" are for starting a text string and the & is for concatenating and finishing the string off.

Does that make it clearer?

**DonkeyOte** · 02-24-2010, 06:00 AM

The COUNTIF is basically accepting strings as criteria, where ranges are used as criteria the values within said cells are the criteria strings.

So it follows that:

<=C363

is not a string whereas:

"<="&C363

is a string
(the <= operator is concatenated with contents of C363 thereby making a string)

To illustrate further, if C363 contained:

="<="&TODAY()

you could simply use:

C363

as the criteria in the COUNTIF given the operator is included in the string being generated in C363.

Does that help at all ?