Utilizing a portion of data in SUMPRODUCT

[Guest]

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 spreadsheet 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?

 

[Bob Phillips]

Peo gave you an answer earlier,

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


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Max]

=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)

 

[Max]

Peo gave you an answer earlier,

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

Thought it was Harald ? <g>

 

[Bob Phillips]

Max,

You may be right, I couldn't find it to check. But I know it was answered.
Apologies to Harald if it were he.

Bob

 

[Guest]

Sorry - Sometimes I get so caught up in trying to conquer something I forget
the niceties of life. I do appreciate everyone's help. And I apologize for
not staying with the same subject; every board has its Rules of Conduct and
obviously I didn't pick up on this one.

I've tried the change you suggested -- adding YEAR -- and end up with a
result of #VALUE!, so I'm obviously still not getting it right. It's so
frustrating when I know something can be done utilizing a formula, but can't
get the data entered correctly to make the formula work!

I'll continue messing with this and may end up coming back to y'all for more
help.

Helen

 

[Bob Phillips]

Helen,

Drop me your workbook, and I will take a look for you.

Bob dot Phillips at tiscali dot co dot uk

--

HTH

RP
(remove nothere from the email address if mailing direct)