Running Calculations per day

  • Thread starter Thread starter Thomas Ryan
  • Start date Start date
T

Thomas Ryan

Column A: days are listed for a year. (1-Jan to 31-Dec)
Column B: contains numbers.

Question: What would be the appropriate formula if you
wanted Excel to SUM each days total from beginning of the
year to today's date automatically, every day. Also, the
remaining numbers to the end of the year automatically?

Thus, if today was 9-July, Excel would automatically
calculate all numbers in Column B from 1-Jan to 9-July.
It would also calcualte the remaining numbers in Column B
from 10-July to 31-Dec. Then next day, Excel would auto-
calculate from 1-Jan to 10-July and from 11-July to 31-
Dec. Etc....
 
I tried this on a range of 20 rows, so you'll have to amend the ranges, but
it seems to work:

For beginning of range(year) through today's date, use:

=SUMIF(A1:A20,"<="&NOW(),B1:B20)

For tommorrow through end of range(year), use:

=SUMIF(A1:A20,">"&NOW(),B1:B20)

HTH
 
Thomas Ryan said:
Column A: days are listed for a year. (1-Jan to 31-Dec)
Column B: contains numbers.

Question: What would be the appropriate formula if you
wanted Excel to SUM each days total from beginning of the
year to today's date automatically, every day. Also, the
remaining numbers to the end of the year automatically?

Thus, if today was 9-July, Excel would automatically
calculate all numbers in Column B from 1-Jan to 9-July.
It would also calcualte the remaining numbers in Column B
from 10-July to 31-Dec. Then next day, Excel would auto-
calculate from 1-Jan to 10-July and from 11-July to 31-
Dec. Etc....
Click to expand...

One way:
=SUMPRODUCT( (A1:A366<=TODAY()) * (B1:B366) )
and
=SUMPRODUCT( (A1:A366>TODAY()) * (B1:B366) )
 
=SUMPRODUCT((ChecksA>DATEVALUE("01/01/2003"))*(ChecksA<NOW()))
or if all the same year just use the sumif <now already given
modify above for the second formula
 
Back
Top