Sum that varies based on the date

I

Iskus23

I need to add the number of training hours for the past year. This range
will change as the months go by. I've tried a number of different formulas
including combinations of formulas, and so far, nothing's worked. I know
this is available, I'm just not remembering how to do it. Any suggestions
would be greatly appreciated! Thanks!

I have a list of dates in one column with the number of hours in the next
column. As an employee takes a training course, we'll add it to the bottom
of the list which is sorted in date order.
 
D

daddylonglegs

If dates are in column A and hours in column B try

=SUMIF(A:A,">"&EDATE(TODAY(),-12),B:B)

EDATE requires Analysis ToolPak enable
 
H

Harlan Grove

Iskus23 wrote...
I couldn't get that to work. If gave me a value of 0. Any other suggestions?
Click to expand...

Four possibilities.
1. The dates in col A are text rather than numbers. Fix: make col A
values numbers.
2. There are no dates in col A later than 7-Aug-2005. No fix if so.
3. You don't have the Analysis ToolPak (ATP) loaded. Fix: load the ATP.
4. The hours in col B are text rather than numbers. Fix: make col B
values numbers.

Of all of these, #3 is most likely. Unlike most other built-in
functions, SUMIF doesn't return an error if its 2nd argument, the
condition, evaluates to an error because error values are valid
conditions themselves. If you didn't have the ATP loaded, then EDATE
would return #NAME?. If you had no #NAME? errors in col A, then SUMIF
would return 0 because no col A values satisfied the condition.
....

Unnecessary for anniversary dates.

=SUMIF(A:A,">"&(TODAY()-365-(DAY(TODAY())<>DAY(TODAY()-365))),B:B)
 
D

daddylonglegs

If you don't have Analysis ToolPak enabled it will return zero. Either
enable analysis ToolPak by

Tools > Add-ins > tick "Analysis ToolPak" box or use

=SUMIF(A:A,">"&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),B:B)

or shorter but posibly les accurate....

...for last 365 days (which may be one day out from a year because of
leap years)

=SUMIF(A:A,">"&TODAY()-365,B:B)
 
Top