Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add Cells in a row depending on Date
Hi,
Using Excel 2003 sp3 and all available addons I am trying to add a row of cells up to the current week. I have the Week Ending in a row number 2 and the payroll values at row number 8. My weeks start at Column C and go through Q, so the Date of the Week Ending starts at C2 and ends at Q2. The values I am trying to add are the values less than today's date; these values are listed in rows C8 through Q8. (Today being a variable and is the current date of the computer) My Date row is formatted to show MM/DD/YYYY but i have even tried changing this to general so it would show the sequential date even though most dates are atuomatically changed to sequential. I tested the today function with this formula: =IF(TODAY()=D2,2,0) returning a 2 or a 0 as I changed the date so I am assuming my problem is in the sum_ranges but I am lost as to what to change! I have tried a few different variations of this formula below: =SUMIF(C2:Q2,"<=TODAY()",C8:Q8) but only get a return of 0! I assume the above formula will sum each cell in row 8 that is less than todays date. I am starting to think this is an incorrect assumption! :( Any help would be much appreciated! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add Cells in a row depending on Date
Here is one way:
=SUM(IF(C2:Q2<=TODAY(),C8:Q8,0)) This is an array formula which must be entered with Ctrl+Shift+Enter and NOT just by pressing Enter. If you do it correctly Excel will put curly brackets around the formula {}. You can't type these yourself. Blank cells in C2:Q2 pass the date test, so their corresponding values in C8:Q8 are picked up. To avoid that, try this SUMPRODUCT formula (does not have to be array-entered): =SUMPRODUCT(--(LEN(C2:Q2)0),--(C2:Q2<=TODAY()),C8:Q8) Hope this helps, Hutch "TTron" wrote: Hi, Using Excel 2003 sp3 and all available addons I am trying to add a row of cells up to the current week. I have the Week Ending in a row number 2 and the payroll values at row number 8. My weeks start at Column C and go through Q, so the Date of the Week Ending starts at C2 and ends at Q2. The values I am trying to add are the values less than today's date; these values are listed in rows C8 through Q8. (Today being a variable and is the current date of the computer) My Date row is formatted to show MM/DD/YYYY but i have even tried changing this to general so it would show the sequential date even though most dates are atuomatically changed to sequential. I tested the today function with this formula: =IF(TODAY()=D2,2,0) returning a 2 or a 0 as I changed the date so I am assuming my problem is in the sum_ranges but I am lost as to what to change! I have tried a few different variations of this formula below: =SUMIF(C2:Q2,"<=TODAY()",C8:Q8) but only get a return of 0! I assume the above formula will sum each cell in row 8 that is less than todays date. I am starting to think this is an incorrect assumption! :( Any help would be much appreciated! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add Cells in a row depending on Date
Try amending your formula to this:
=SUMIF(C2:Q2,"<="&TODAY(),C8:Q8) Hope this helps. Pete "TTron" wrote in message ... Hi, Using Excel 2003 sp3 and all available addons I am trying to add a row of cells up to the current week. I have the Week Ending in a row number 2 and the payroll values at row number 8. My weeks start at Column C and go through Q, so the Date of the Week Ending starts at C2 and ends at Q2. The values I am trying to add are the values less than today's date; these values are listed in rows C8 through Q8. (Today being a variable and is the current date of the computer) My Date row is formatted to show MM/DD/YYYY but i have even tried changing this to general so it would show the sequential date even though most dates are atuomatically changed to sequential. I tested the today function with this formula: =IF(TODAY()=D2,2,0) returning a 2 or a 0 as I changed the date so I am assuming my problem is in the sum_ranges but I am lost as to what to change! I have tried a few different variations of this formula below: =SUMIF(C2:Q2,"<=TODAY()",C8:Q8) but only get a return of 0! I assume the above formula will sum each cell in row 8 that is less than todays date. I am starting to think this is an incorrect assumption! :( Any help would be much appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select a Range of column depending on the date | Excel Worksheet Functions | |||
hide button depending on date | Excel Worksheet Functions | |||
hiding rows depending on the date | Excel Discussion (Misc queries) | |||
Inserting a Date depending on a spereate Cell Value | Excel Discussion (Misc queries) | |||
Want to show/hide cells depending on other cells results | Excel Discussion (Misc queries) |