Sum by ID#

[Intern13]

I need a formula that will calculate the sum hours of a specific ID # and also have it only calculate hours if they are from a date newer than today-365. So basically it will only count hours that are from within a year ago today. I attached a very small example.

[tlafferty]

Enter this formula in E2 and fill down:
=IF(TODAY()-365<C2,D2,0)

Enter the Label ID: in cell A6
Enter the ID you want to sum in B6
Enter the label Hours: in cell A7

Enter this formula in cell B7:
=SUMPRODUCT(--(B2:B4=B6)*(E2:E4))

NOTE: all of the data provided will provide a sum since it's all less than a year old...

[Intern13]

Thank you I think that works perfectly. I appreciate the quick and accurate response.

[Cutter]

I think for more accuracy rather than TODAY()-365 (because sometimes it's 366) you should use this in E2 and down:

=IF(C2="","",IF(C2>EDATE(TODAY(),-12),D2,""))

And SUMIF() is preferable to SUMPRODUCT() so:

=SUMIF(B2:B4,H2,E2:E4) with the ID number you want summed sitting in cell H2.

If you wish to add another criteria you can use SUMIFS() (available for Excel 2007 and up).