SUMPRODUCT Problem

  • Thread starter Thread starter Blake
  • Start date Start date
B

Blake

=SUMPRODUCT(--(C39:C5000=D20),--(D39:D5000=D24),--(E39:E5000>0))

I'm having a problem with the above formula, and I think it has to do
with formatting.

In column b is a list of dates and column c has this formula pulled
down to give me the weekday.

=IF(B39="","",B39) formatted (ddd)

D20 refers to the day of the week and is also formatted (ddd)

D24 refers to a cell in column D and is just text.

It answers 0 when it should be three.

Can't figure out what I'm doing wrong. I'm sure it's a formatting
problem, because I have the exact formula elsewhere, and it works
fine, but in those formulas cell D20 refers to a date--not weekday.

Thanks
 
=SUMPRODUCT(--(C39:C5000=D20),--(D39:D5000=D24),
--(E39:E5000>0)) [....]
In column b is a list of dates and column c has this
formula pulled down to give me the weekday.
=IF(B39="","",B39) formatted (ddd)

D20 refers to the day of the week and is also formatted (ddd)
Click to expand...

I suspect that part of your problem is: D20 contains __text__ that
appears to be Mon, Tue, etc.

But even if D20 contains a numeric date that happens to be on the same
day of the week as some dates in column C, you cannot expect them to
match simply because you formatted them to display only the day of the
week (Custom ddd). Formatting affects only the appearance of values;
it does not change the underlying value (numeric date).

First, try the following:

=SUMPRODUCT(--(WEEKDAY(C39:C5000)=WEEKDAY(D20)),
--(D39:D5000=D24),--(E39:E5000>0))

It is very likely that will not work because it seems likely that D20
contains the day of the week as text instead of a numeric date.

In that case, try:

=SUMPRODUCT(--(TEXT(C39:C5000,"ddd")=D20),
--(D39:D5000=D24),--(E39:E5000>0))
 
=SUMPRODUCT(--(C39:C5000=D20),--(D39:D5000=D24),
--(E39:E5000>0)) [....]
In column b is a list of dates and column c has this
formula pulled down to give me the weekday.
=IF(B39="","",B39) formatted (ddd)
Click to expand...
D20 refers to the day of the week and is also formatted (ddd)
Click to expand...

I suspect that part of your problem is:  D20 contains __text__ that
appears to be Mon, Tue, etc.

But even if D20 contains a numeric date that happens to be on the same
day of the week as some dates in column C, you cannot expect them to
match simply because you formatted them to display only the day of the
week (Custom ddd).  Formatting affects only the appearance of values;
it does not change the underlying value (numeric date).

First, try the following:

=SUMPRODUCT(--(WEEKDAY(C39:C5000)=WEEKDAY(D20)),
--(D39:D5000=D24),--(E39:E5000>0))

It is very likely that will not work because it seems likely that D20
contains the day of the week as text instead of a numeric date.

In that case, try:

=SUMPRODUCT(--(TEXT(C39:C5000,"ddd")=D20),
--(D39:D5000=D24),--(E39:E5000>0))
Click to expand...

Thanks to all who responded I learned some things. I changed the
formula of Column C to =Weekday(b39) or whatever. Now whatever date
is in column B, column C will give the proper day of the week, and
this seems to have solved the problem.
 
Back
Top