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

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

T. Valko

Try one of these:

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

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

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),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
 
D

Dave Peterson

=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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top