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!

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!

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!