# Dates and days of the week...

1. ## Dates and days of the week...

Hello all,

I have a huge worksheet with multiple dates listed like this...

1/1/2001
1/2/2001
1/3/2001
...
3/16/2016

on another tab I have
Monday,
Tuesday,
Wednesday,
Thursday,
Friday

I want to put a formula in the cell next to the weekday that basically says if date = Monday give me sum of all Mondays.
same for Tuesday... etc.

Thank you all.

2. ## Re: Dates and days of the week...

Originally Posted by shhhhh22
give me sum of all Mondays.
Do you mean return the COUNT of all Mondays?

3. ## Re: Dates and days of the week...

See if this is what you had in mind...

Data Range
 A B C D E 1 Date Weekday Count 2 12/30/2009 Monday 1 3 6/18/2002 Tuesday 3 4 9/7/2004 Wednesday 3 5 11/21/2013 Thursday 3 6 4/17/2014 Friday 0 7 9/1/2002 Saturday 2 8 6/5/2011 Sunday 2 9 8/5/2009 ------ ------ ------ ------ 10 9/19/2013 11 3/26/2012 12 9/12/2009 13 4/22/2000 14 2/1/2012 15 12/30/2014

This formula entered in E2 and copied down:

=SUMPRODUCT(--(TEXT(A\$2:A\$15,"dddd")=D2))

4. ## Re: Dates and days of the week...

thank you Tony... I should have been more clear. If we assume there are 10 years worth of dates in column "A" just as you have... in column "B" is a value. I want to add those values based on day of the week.

5. ## Re: Dates and days of the week...

Like this...

Data Range
 A B C D E 1 Date Value Weekday Total 2 12/30/2009 37 Monday 78 3 6/18/2002 74 Tuesday 204 4 9/7/2004 46 Wednesday 164 5 11/21/2013 54 Thursday 165 6 4/17/2014 16 Friday 0 7 9/1/2002 3 Saturday 39 8 6/5/2011 63 Sunday 66 9 8/5/2009 43 ------ ------ ------ 10 9/19/2013 95 11 3/26/2012 78 12 9/12/2009 10 13 4/22/2000 29 14 2/1/2012 84 15 12/30/2014 84

This formula entered in E2 and copied down:

=SUMPRODUCT(--(TEXT(A\$2:A\$15,"dddd")=D2),B\$2:B\$15)

6. ## Re: Dates and days of the week...

This is perfect. Bless you. Thanks!

7. ## Re: Dates and days of the week...

You're welcome. Thanks for the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1