| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF by Week.
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF by Week.
I want to use this week.
Does "this week" run from Monday to Sunday? -- Biff Microsoft Excel MVP "GEM" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF by Week.
Yes!! I want it to run from Monday - Sunday
"T. Valko" wrote: I want to use this week. Does "this week" run from Monday to Sunday? -- Biff Microsoft Excel MVP "GEM" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF by Week.
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 -- Biff Microsoft Excel MVP "GEM" wrote in message ... Yes!! I want it to run from Monday - Sunday "T. Valko" wrote: I want to use this week. Does "this week" run from Monday to Sunday? -- Biff Microsoft Excel MVP "GEM" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF by Week.
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" wrote: 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 -- Biff Microsoft Excel MVP "GEM" wrote in message ... Yes!! I want it to run from Monday - Sunday "T. Valko" wrote: I want to use this week. Does "this week" run from Monday to Sunday? -- Biff Microsoft Excel MVP "GEM" wrote in message ... 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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF by Week.
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") -- Biff Microsoft Excel MVP "GEM" wrote in message ... 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" wrote: 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 -- Biff Microsoft Excel MVP "GEM" wrote in message ... Yes!! I want it to run from Monday - Sunday "T. Valko" wrote: I want to use this week. Does "this week" run from Monday to Sunday? -- Biff Microsoft Excel MVP "GEM" wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF by Week.
Another option...
Create a name for the formula. Goto InsertNameDefine 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. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... 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") -- Biff Microsoft Excel MVP "GEM" wrote in message ... 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" wrote: 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 -- Biff Microsoft Excel MVP "GEM" wrote in message ... Yes!! I want it to run from Monday - Sunday "T. Valko" wrote: I want to use this week. Does "this week" run from Monday to Sunday? -- Biff Microsoft Excel MVP "GEM" wrote in message ... 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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF by Week.
Works great!!! Thank you very much!!!
"T. Valko" wrote: Another option... Create a name for the formula. Goto InsertNameDefine 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. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... 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") -- Biff Microsoft Excel MVP "GEM" wrote in message ... 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" wrote: 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 -- Biff Microsoft Excel MVP "GEM" wrote in message ... Yes!! I want it to run from Monday - Sunday "T. Valko" wrote: I want to use this week. Does "this week" run from Monday to Sunday? -- Biff Microsoft Excel MVP "GEM" wrote in message ... 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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF by Week.
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "GEM" wrote in message ... Works great!!! Thank you very much!!! "T. Valko" wrote: Another option... Create a name for the formula. Goto InsertNameDefine 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. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... 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") -- Biff Microsoft Excel MVP "GEM" wrote in message ... 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" wrote: 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 -- Biff Microsoft Excel MVP "GEM" wrote in message ... Yes!! I want it to run from Monday - Sunday "T. Valko" wrote: I want to use this week. Does "this week" run from Monday to Sunday? -- Biff Microsoft Excel MVP "GEM" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert regular Date to Week Ending or Week Beginning Dates | Excel Discussion (Misc queries) | |||
| SUMIF? week, month & year-to-date totals | Excel Worksheet Functions | |||
| Auto calculate day of week to week of the year (not as serial) | Excel Discussion (Misc queries) | |||
| How do I set up a week by week skill training schedule in excel? | Excel Discussion (Misc queries) | |||
| I need week number in excell from a date, first week must be mini. | Excel Discussion (Misc queries) | |||
Linear Mode
