Utilizing a portion of data in SUMPRODUCT
 

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?

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)


"Helen McClaine" <Helen wrote in message
...
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?

=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 <atyahoo<dotcom
----
"Helen McClaine" <Helen wrote in message
...
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" wrote
Peo gave you an answer earlier,

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

Thought it was Harald ? <g
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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


"Max" wrote in message
...
"Bob Phillips" wrote
Peo gave you an answer earlier,

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

Thought it was Harald ? <g
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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

"Max" wrote:

=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 <atyahoo<dotcom
----
"Helen McClaine" <Helen wrote in message
...
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?

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)


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

"Max" wrote:

=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 <atyahoo<dotcom
----
"Helen McClaine" <Helen wrote in
message
...
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?

Thanks for the feedback, Helen

Trust you've got it sorted out by now with Bob's help

Just some comments:

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

I've tried the change you suggested -- adding YEAR
-- and end up with a result of #VALUE! ..

Think some/all of the data in Log!B3:B80
may not be real dates/numbers
(dates are actually numbers in Excel)

(All it needs is a single cell in B3:B80 which
is not a real date, and you'll get #VALUE! <g)

Try this quick check in the sheet Log
in an empty col to the right

Put a label into G2: Check (say)

Put in G3: =ISNUMBER(B3+0)
Copy down to G80

[G3:G80 should evaluate to TRUE
for real dates/numbers]

Select G2:G80
Do a Data Filter Autofilter on the range
Click on FALSE in the droplist in G2 to filter these out
Look at what's in col B and try re-entering the date(s)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi Max,

You will not be surprised to hear that the problem was with the data, some
of the fields had two dates in them, therefore the YEAR function errored on
what was actually a string field.

We changed the formula to SUM(IF(... to handle the errors, and it seems okay
now.

Regards

Bob


"Max" wrote in message
...
Thanks for the feedback, Helen

Trust you've got it sorted out by now with Bob's help

Just some comments:

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

I've tried the change you suggested -- adding YEAR
-- and end up with a result of #VALUE! ..

Think some/all of the data in Log!B3:B80
may not be real dates/numbers
(dates are actually numbers in Excel)

(All it needs is a single cell in B3:B80 which
is not a real date, and you'll get #VALUE! <g)

Try this quick check in the sheet Log
in an empty col to the right

Put a label into G2: Check (say)

Put in G3: =ISNUMBER(B3+0)
Copy down to G80

[G3:G80 should evaluate to TRUE
for real dates/numbers]

Select G2:G80
Do a Data Filter Autofilter on the range
Click on FALSE in the droplist in G2 to filter these out
Look at what's in col B and try re-entering the date(s)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thanks for the update, Bob !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Bob Phillips" wrote in message
...
Hi Max,

You will not be surprised to hear that the problem was with the data, some
of the fields had two dates in them, therefore the YEAR function errored
on
what was actually a string field.

We changed the formula to SUM(IF(... to handle the errors, and it seems
okay
now.

Regards

Bob