Using Countif for Date Matches

[Guest]

Hello. I am trying to count the number of sales that occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D14,year=2004), but all I get is 0, when
I thought I would get 1, which represented the sale for 2004.
Can anyone help?

Data
01/01/2003
03/15/2003
06/01/2004
09/30/2005

 

[Guest]

Perhaps something like:
=SUMPRODUCT(--(YEAR(SalesForecast!D14)=2004))

For an explanation of what is going on, see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

tj

 

[Guest]

One way

=SUMPRODUCT(--(YEAR(SalesForecast!D14)=2004))


Regards,

Peo Sjoblom

 

[Don Guillett]

try
=sumproduct((year(SalesForecast!D14)=2004)*1)

 

[Jason Morin]

A few ways:

=SUMPRODUCT(--(YEAR(A1:A4)=2004))
=COUNTIF(A1:A4,">=1/1/04")-COUNTIF(A1:A4,">12/31/04")
=SUM(COUNTIF(A1:A4,{">=1/1/04",">12/31/04"})*{1,-1})

HTH
Jason
Atlanta, GA

 

[Guest]

tj

That looks so simple, but I get !VALUE# using this formula:

=SUMPRODUCT(--(YEAR(A$1:A182)=2004))

Col A is all dates. What's wrong?

TIA

 

[Guest]

How are you typing your dates compared to your system settings?

I'm using US dates: 12/26/2004 means December 26th of 2004. If I type
26/12/2004 in my range, I get #VALUE.

I copied and pasted your formula, and it worked fine. I suspect you are
typing your dates in a way your operating system isn't recognizing as a date.

(If your) In Windows, go into the Control Panel and find the Regional
Settings. Do the settings for your Date Formats look the same as how you
typed Dtes in Excel? If not, that is the problem.

tj

 

[Guest]

tj,

That's an interesting idea, but doesn't apply here. My dates are mm/dd/yyyy
both in the spreadsheet and in the control pannel.

 

[RagDyeR]

I have to agree that the problem is probably your data in the search column.

Exactly how is Column A populated?

Keyed in ... result of formula ... import from other source?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

tj,

That's an interesting idea, but doesn't apply here. My dates are mm/dd/yyyy
both in the spreadsheet and in the control pannel.

 

[Guest]

Rag,

The daya is keyed. I can sort the data and it appears correct. If there
were something amiss, the errors would be at the top or bottom, I think.

 

[RagDyer]

Couple of ways I could duplicate the VALUE! error with correctly formatted
date data, was to put a <Space> in front of the date, and/or have *alpha*
text somewhere within the range.
Numeric text is accepted.
Even a space entered in front of a *single* date in the entire range
produced the #VALUE! error, even if the date was not one of the ones that
met the criteria.

Any possibility of that?

Try this formula:

=SUMPRODUCT(--(YEAR(TRIM(A$1:A182))=2004))

This will take care of any cells that may contain a leading space.
However, the caveat here, is that with this TRIM() inserted, you *CANNOT*
have any empty cells in the range (A1:A182).

Also, what happens when you try the second and third formulas of Jason?
Either of those should work with text or spaces.
--

HTH,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Rag,

The daya is keyed. I can sort the data and it appears correct. If there
were something amiss, the errors would be at the top or bottom, I think.

 

[Guest]

Rag,

You're right in that those errors *would* cause my problem, but it turns out
that's not the source of the problem. A1 contains a heading (date.) When I
changed the array to $a$2 the error disappeared.

Thanks so much for your helpful attention to my problem. My final formula,
where I was truly heading is:
=SUMPRODUCT(--(MONTH(A$2:$A183)=MONTH($A183)),C$2:$C183)

This gives me the running total by month. Now it works as it should.

Thanks, again.

 

[RagDyer]

Thanks for the feed-back.

And I'll bet that *THAT* WAS* the source of the problem, the header being
*TEXT*.
If the header was a "true" date, formatted to display whatever you wanted to
see, the formula would have worked!
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Rag,

You're right in that those errors *would* cause my problem, but it turns out
that's not the source of the problem. A1 contains a heading (date.) When I
changed the array to $a$2 the error disappeared.

Thanks so much for your helpful attention to my problem. My final formula,
where I was truly heading is:
=SUMPRODUCT(--(MONTH(A$2:$A183)=MONTH($A183)),C$2:$C183)

This gives me the running total by month. Now it works as it should.

Thanks, again.

 

[Ron Rosenfeld]

Hello. I am trying to count the number of sales that occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D14,year=2004), but all I get is 0, when
I thought I would get 1, which represented the sale for 2004.
Can anyone help?

Data
01/01/2003
03/15/2003
06/01/2004
09/30/2005

=COUNTIF(SalesForecast!D14,">=" & DATE(2004,1,1)) -
COUNTIF(SalesForecast!D14,">" & DATE(2004,12,31))


--ron