SUMIF by Week.

[GEM]

How can I sum a range of cells, identifing them by week.

For example, I have column B with dates, and colum C has different numbers.
I want to use a SUMIF function on A1 that automatically adds different cells
from column C which are included in this week.

Something like this, =SUMIF(B1:B100,TODAY(),C1:C100) but instead
of TODAY(), I want to use this week.

 

[T. Valko]

I want to use this week.

Does "this week" run from Monday to Sunday?

 

[GEM]

Yes!! I want it to run from Monday - Sunday

Does "this week" run from Monday to Sunday?

 

[T. Valko]

Try this:

This will return the Monday date of the current week:

=TODAY()-WEEKDAY(NOW(),3)

Format as Date (if Excel doesn't automatically do it for you).

Incorporated into a SUMIF formula:

=SUM(SUMIF(B1:B100,TODAY()-WEEKDAY(NOW(),3)+{0,1,2,3,4,5,6},C1:C100))

Or, you could enter the date formula in a separate cell and then refer to
it:

E1: =TODAY()-WEEKDAY(NOW(),3)

=SUMIF(B1:B100,">="&E1,C1:C100)-SUMIF(B1:B100,">"&E1+6,C1:C100)

=SUMPRODUCT(--(B1:B100>=E1),--(B1:B100<=E1+6),C1:C100)

Format the sum cells as General or Number

 

[GEM]

Thanks!! Worked great!! Is there anyway a cell can show you what this week
is, something like when you enter =TODAY() into a cell, it shows you todays
date. This week I would like it to show, July 13, 2009 - July 19, 2009, next
week July 20, 2009 - July 26, 2009, and so on...

 

[T. Valko]

Try this (all on one line):

=TEXT(TODAY()-WEEKDAY(NOW(),3),"mmmm d, yyyy")
&" - "&TEXT(TODAY()-WEEKDAY(NOW(),3)+6
,"mmmm d, yyyy")

Or, using cell A1 to return the Monday date of the current week...

A1: =TODAY()-WEEKDAY(NOW(),3)

Then (all on one line):

=TEXT(A1,"mmmm d, yyyy")&" - "
&TEXT(A1+6,"mmmm d, yyyy")

 

[T. Valko]

Another option...

Create a name for the formula.
Goto Insert>Name>Define
Name: ThisWeek
Refers to:

=TEXT(TODAY()-WEEKDAY(NOW(),3),"mmmm d, yyyy")
&" - "&TEXT(TODAY()-WEEKDAY(NOW(),3)+6
,"mmmm d, yyyy")

OK

Then this formula entered in a cell: =ThisWeek

I think I like this option the most.

 

[GEM]

Works great!!! Thank you very much!!!

Another option...

Create a name for the formula.
Goto Insert>Name>Define
Name: ThisWeek
Refers to:

=TEXT(TODAY()-WEEKDAY(NOW(),3),"mmmm d, yyyy")
&" - "&TEXT(TODAY()-WEEKDAY(NOW(),3)+6
,"mmmm d, yyyy")

OK

Then this formula entered in a cell: =ThisWeek

I think I like this option the most.

 

[T. Valko]

You're welcome. Thanks for the feedback!