SUMPRODUCT Problem

[Blake]

=SUMPRODUCT(--(C39:C5000=D20),--(D395000=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

 

[Ron Rosenfeld]

=SUMPRODUCT(--(C39:C5000=D20),--(D395000=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

In general, formatting is irrelevant. The contents of the cell IS relevant.

How does D20 "refer to the day of the week"?
What is in D395000?
What is in E39:E5000?

In answering the above, copy/paste the contents from the FORMULA BAR, not from the cell.

 

[joeu2004]

=SUMPRODUCT(--(C39:C5000=D20),--(D395000=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)

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)),
--(D395000=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),
--(D395000=D24),--(E39:E5000>0))

 

[Blake]

=SUMPRODUCT(--(C39:C5000=D20),--(D395000=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)

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)),
--(D395000=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),
--(D395000=D24),--(E39:E5000>0))

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.