Hello. I am trying to count the number of sales that occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D1:D4,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

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

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

tj

"Raymond Gallegos" wrote:

Hello. I am trying to count the number of sales that occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D1:D4,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

One way

=SUMPRODUCT(--(YEAR(SalesForecast!D1:D4)=2004))


Regards,

Peo Sjoblom

"Raymond Gallegos" wrote:

Hello. I am trying to count the number of sales that occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D1:D4,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

try
=sumproduct((year(SalesForecast!D1:D4)=2004)*1)

--
Don Guillett
SalesAid Software

"Raymond Gallegos" wrote in
message ...
Hello. I am trying to count the number of sales that occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D1:D4,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

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

-----Original Message-----
Hello. I am trying to count the number of sales that
occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D1:D4,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
.

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

"tjtjjtjt" wrote:

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

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

tj

"Raymond Gallegos" wrote:

Hello. I am trying to count the number of sales that occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D1:D4,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

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

"JBoulton" wrote:

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

"tjtjjtjt" wrote:

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

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

tj

"Raymond Gallegos" wrote:

Hello. I am trying to count the number of sales that occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D1:D4,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

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.



"tjtjjtjt" wrote:

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

"JBoulton" wrote:

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

"tjtjjtjt" wrote:

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

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

tj

"Raymond Gallegos" wrote:

Hello. I am trying to count the number of sales that occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D1:D4,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

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 !
--------------------------------------------------------------------

"JBoulton" wrote in message
...
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.



"tjtjjtjt" wrote:

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

"JBoulton" wrote:

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

"tjtjjtjt" wrote:

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

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

tj

"Raymond Gallegos" wrote:

Hello. I am trying to count the number of sales that occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D1:D4,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

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" wrote:

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 !
--------------------------------------------------------------------

"JBoulton" wrote in message
...
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.



"tjtjjtjt" wrote:

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

"JBoulton" wrote:

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

"tjtjjtjt" wrote:

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

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

tj

"Raymond Gallegos" wrote:

Hello. I am trying to count the number of sales that occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D1:D4,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

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!
-------------------------------------------------------------------

"JBoulton" wrote in message
...
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" wrote:

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 !
--------------------------------------------------------------------

"JBoulton" wrote in message
...
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.



"tjtjjtjt" wrote:

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

"JBoulton" wrote:

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

"tjtjjtjt" wrote:

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

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

tj

"Raymond Gallegos" wrote:

Hello. I am trying to count the number of sales that occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D1:D4,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

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" wrote:

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!
-------------------------------------------------------------------

"JBoulton" wrote in message
...
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" wrote:

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 !
--------------------------------------------------------------------

"JBoulton" wrote in message
...
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.



"tjtjjtjt" wrote:

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

"JBoulton" wrote:

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

"tjtjjtjt" wrote:

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

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

tj

"Raymond Gallegos" wrote:

Hello. I am trying to count the number of sales that occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D1:D4,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

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!
-------------------------------------------------------------------

"JBoulton" wrote in message
...
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" wrote:

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!
-------------------------------------------------------------------

"JBoulton" wrote in message
...
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" wrote:

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 !
--------------------------------------------------------------------

"JBoulton" wrote in message
...
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.



"tjtjjtjt" wrote:

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

"JBoulton" wrote:

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

"tjtjjtjt" wrote:

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

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

tj

"Raymond Gallegos" wrote:

Hello. I am trying to count the number of sales that occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D1:D4,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

On Thu, 23 Dec 2004 08:45:02 -0800, "Raymond Gallegos"
wrote:

Hello. I am trying to count the number of sales that occurred
in 2004 by using the following Countif formula
=COUNTIF(SalesForecast!D1:D4,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!D1:D4,"=" & DATE(2004,1,1)) -
COUNTIF(SalesForecast!D1:D4,"" & DATE(2004,12,31))


--ron