Date formula

[onthepitch]

I'm trying to count data in a spreadsheet on a few selections. One of those being a dynamic date. Here's what I've got.

I generate 4 different dates using the following formula or similar. It starts with a start date in cell AC2

=DATE(YEAR(AC2),MONTH(AC2)-1,DAY(AC2)-1)

From there, I get 4 dates.

Start End
2/21/2010 3/22/2010
3/21/2010 4/22/2010
4/21/2010 5/22/2010

The spreadsheet I'm trying to count looks like this.

Owner Name Date
Bill USC 3/20/2010

I'm trying to count the number of records less than 3/22/2010 which is one of the dates I formulated above. I use the following formula. The first half referencing AC3 is to reference the date. The second to reference the Name.

=COUNTIFS(Funnel!$F:$F,"<=AC3",Funnel!$B:$B,B9)

The problem is that I only return a 0. If I replace AC3 above with 3/22/2010, I return a value so the formula is counting correctly but not reading the date accurately.

Thoughts?

[darkyam]

Use "<="&AC3 instead.

[onthepitch]

Holly Smokes! That was it. Can you tell my what that does?

[darkyam]

The difference was that when you have cell references in text, it doesn't read them as cell references, but as text. It was measuring to see if the date was below "AC3", but since all text is considered to be above all numbers, it returned 0. When you replaced AC3 with a number, then it calculated correctly.