Re: Utilizing a portion of data in SUMPRODUCT

From: Max (demechanik_at_yahoo.com)
Date: 01/25/05 

Date: Wed, 26 Jan 2005 06:54:15 +0800

> =SUMPRODUCT(--(Log!B3:B80=1997),--(Log!E3:E80=17))
> The data in Column B is actually a full date, entered yyyy/mm/dd.

Try using a YEAR() for the 1st condition:

=SUMPRODUCT(--(YEAR(Log!B3:B80)=1997),--(Log!E3:E80=17))

Try to stay within the same thread for easier follow-up. Noted you've posted
a couple of queries (which seem related) and received good responses from
others, but you've yet to *reply* to any of these responses given. (You
could find / click on the "reply" button in the web interface) 

--
Rgds
Max
xl 97
---
GMT+8,  1° 22' N  103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Helen McClaine" <Helen McClaine@discussions.microsoft.com> wrote in message
news:296AB786-B1B0-471B-B871-6FF01D96EE50@microsoft.com...
> Based on prior input from the group, I am using the following formula:
>
> =SUMPRODUCT(--(Log!B3:B80=1997),--(Log!E3:E80=17))
>
> The data in Column B is actually a full date, entered yyyy/mm/dd.  With
the
> data in this format, I get a formula result of 0.  If I eliminate the
/mm/dd,
> I get the correct result of 1.  I have tried the formula with and without
> quotes around the Condition.
>
> The person for whom I am creating this spread*** has asked that the date
> be kept together, rather than separating out the year.  Is there some way
I
> can maintain the data in Column B and change the formula to get the
correct
> result?
>
>