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?

Try one of these:

=SUMPRODUCT(--(WEEKDAY(A2:A20)=2),D2:D20)

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


--
Biff
Microsoft Excel MVP


"al" wrote in message news:djSDj.2924$bN3.2476@trnddc03...
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?

On Tue, 18 Mar 2008 13:30:09 -0400, T. Valko wrote:

Try one of these:

=SUMPRODUCT(--(WEEKDAY(A2:A20)=2),D2:D20)

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

Thanks a million, first one did the trick :)

-AL

"al" wrote in message news:cLTDj.2938$bN3.1504@trnddc03...
On Tue, 18 Mar 2008 13:30:09 -0400, T. Valko wrote:

Try one of these:

=SUMPRODUCT(--(WEEKDAY(A2:A20)=2),D2:D20)

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

Thanks a million, first one did the trick :)

-AL

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

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),D8:D153)} This currently adds the
values in D8:D153 if the day happens to be a Monday in A8:A153, I would
ideally like it to only add the values in D8:D153 if G8:G153 are empty...

Any ideas?

Thanks again!

-AL

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

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



al wrote:

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),D8:D153)} This currently adds the
values in D8:D153 if the day happens to be a Monday in A8:A153, I would
ideally like it to only add the values in D8:D153 if G8:G153 are empty...

Any ideas?

Thanks again!

-AL

--

Dave Peterson

Perfect! Thanks Dave, that did the trick.