Receive #VALUE! when I mix text with dates using SUMPRODUCT
 

Hello,

I'm trying to get a count of the number of times a specific year (2007 in
this case) is displayed under one column using this formula:

=SUMPRODUCT(--(YEAR(C6:C52)=2007))

Everything works fine until anything but a date is entered within column C,
which it then gives me the #VALUE! error. Is there any way that SUMPRODUCT
can bypass or ignore those cells which include text?

Thanks,
Rick

Receive #VALUE! when I mix text with dates using SUMPRODUCT
 

Hi Rick,

Try something like this ...

=SUMPRODUCT(--(YEAR(C6:C52)=2007),--ISNUMBER(C6:C52))

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Rick" wrote in message
...
Hello,

I'm trying to get a count of the number of times a specific year (2007 in
this case) is displayed under one column using this formula:

=SUMPRODUCT(--(YEAR(C6:C52)=2007))

Everything works fine until anything but a date is entered within column
C,
which it then gives me the #VALUE! error. Is there any way that
SUMPRODUCT
can bypass or ignore those cells which include text?

Thanks,
Rick

Receive #VALUE! when I mix text with dates using SUMPRODUCT
 

Nope, still the same thing. I tried switching the ISNUMBER function to the
first array as well, and still no luck.

"Zack Barresse" wrote:

Hi Rick,

Try something like this ...

=SUMPRODUCT(--(YEAR(C6:C52)=2007),--ISNUMBER(C6:C52))

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Rick" wrote in message
...
Hello,

I'm trying to get a count of the number of times a specific year (2007 in
this case) is displayed under one column using this formula:

=SUMPRODUCT(--(YEAR(C6:C52)=2007))

Everything works fine until anything but a date is entered within column
C,
which it then gives me the #VALUE! error. Is there any way that
SUMPRODUCT
can bypass or ignore those cells which include text?

Thanks,
Rick

Receive #VALUE! when I mix text with dates using SUMPRODUCT
 

Try...

=SUMPRODUCT(--ISNUMBER(1/(YEAR(C6:C52)=2007)))

Hope this helps!

In article ,
Rick wrote:

Hello,

I'm trying to get a count of the number of times a specific year (2007 in
this case) is displayed under one column using this formula:

=SUMPRODUCT(--(YEAR(C6:C52)=2007))

Everything works fine until anything but a date is entered within column C,
which it then gives me the #VALUE! error. Is there any way that SUMPRODUCT
can bypass or ignore those cells which include text?

Thanks,
Rick

Receive #VALUE! when I mix text with dates using SUMPRODUCT
 

Try this:

=SUMPRODUCT(--(TEXT(C6:C52,"yyyy")="2007"))

Biff

"Rick" wrote in message
...
Nope, still the same thing. I tried switching the ISNUMBER function to
the
first array as well, and still no luck.

"Zack Barresse" wrote:

Hi Rick,

Try something like this ...

=SUMPRODUCT(--(YEAR(C6:C52)=2007),--ISNUMBER(C6:C52))

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Rick" wrote in message
...
Hello,

I'm trying to get a count of the number of times a specific year (2007
in
this case) is displayed under one column using this formula:

=SUMPRODUCT(--(YEAR(C6:C52)=2007))

Everything works fine until anything but a date is entered within
column
C,
which it then gives me the #VALUE! error. Is there any way that
SUMPRODUCT
can bypass or ignore those cells which include text?

Thanks,
Rick

Receive #VALUE! when I mix text with dates using SUMPRODUCT
 

BEAUTIFUL! Thanks a lot!

-Rick

"Biff" wrote:

Try this:

=SUMPRODUCT(--(TEXT(C6:C52,"yyyy")="2007"))

Biff

"Rick" wrote in message
...
Nope, still the same thing. I tried switching the ISNUMBER function to
the
first array as well, and still no luck.

"Zack Barresse" wrote:

Hi Rick,

Try something like this ...

=SUMPRODUCT(--(YEAR(C6:C52)=2007),--ISNUMBER(C6:C52))

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Rick" wrote in message
...
Hello,

I'm trying to get a count of the number of times a specific year (2007
in
this case) is displayed under one column using this formula:

=SUMPRODUCT(--(YEAR(C6:C52)=2007))

Everything works fine until anything but a date is entered within
column
C,
which it then gives me the #VALUE! error. Is there any way that
SUMPRODUCT
can bypass or ignore those cells which include text?

Thanks,
Rick

Receive #VALUE! when I mix text with dates using SUMPRODUCT
 

You're welcome!

Biff

"Rick" wrote in message
...
BEAUTIFUL! Thanks a lot!

-Rick

"Biff" wrote:

Try this:

=SUMPRODUCT(--(TEXT(C6:C52,"yyyy")="2007"))

Biff

"Rick" wrote in message
...
Nope, still the same thing. I tried switching the ISNUMBER function to
the
first array as well, and still no luck.

"Zack Barresse" wrote:

Hi Rick,

Try something like this ...

=SUMPRODUCT(--(YEAR(C6:C52)=2007),--ISNUMBER(C6:C52))

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Rick" wrote in message
...
Hello,

I'm trying to get a count of the number of times a specific year
(2007
in
this case) is displayed under one column using this formula:

=SUMPRODUCT(--(YEAR(C6:C52)=2007))

Everything works fine until anything but a date is entered within
column
C,
which it then gives me the #VALUE! error. Is there any way that
SUMPRODUCT
can bypass or ignore those cells which include text?

Thanks,
Rick