Excel 2000: Adding column D based on value in column A

[al]

Hi,

In a worksheet I have dates in column A (format
03/18/2008,/03/17/2008,03/17/2008) and in column D i have numbers (format
0.4,1.0.3).

I am looking for a formula to total all of the numbers where the date
happens to be a Monday - example:

Column A Column D
3/17/2008 0.5
3/17/2008 1
3/18/2008 2

I would like a formula that returns "1.5" because 3/17/2008 is a Monday.
Any ideas?

 

[T. Valko]

Try one of these:

=SUMPRODUCT(--(WEEKDAY(A2:A20)=2),D220)

=SUMPRODUCT(--(TEXT(A2:A20,"ddd")="Mon"),D220)

 

[al]

Try one of these:

=SUMPRODUCT(--(WEEKDAY(A2:A20)=2),D220)

=SUMPRODUCT(--(TEXT(A2:A20,"ddd")="Mon"),D220)

Thanks a million, first one did the trick

-AL

 

[T. Valko]

Thanks a million, first one did the trick

-AL

You're welcome. Thanks for the feedback!

 

[al]

I'm hoping for some help expanding on the formula:

{=SUM(--TEXT(A8:A153,"dddd")="Monday"))} This currently returns the count
of instances where a date falls on Monday - I am hoping to only count
them if G8:G153 is empty

and the same with:

{=SUMPRODUCT(--(WEEKDAY(A8:A153)=2),D8153)} This currently adds the
values in D8153 if the day happens to be a Monday in A8:A153, I would
ideally like it to only add the values in D8153 if G8:G153 are empty...

Any ideas?

Thanks again!

-AL

 

[Dave Peterson]

=SUMproduct(--(TEXT(A8:A153,"dddd")="Monday"),--(g8:g153=""))
and
=SUMPRODUCT(--(WEEKDAY(A8:A153)=2),--(g8:g153=""),D8153)

Using =sumproduct() means that you don't need to array enter the formula.

 

[al]

Perfect! Thanks Dave, that did the trick.