Sum Totals in a 3rd column from other columns using conditional formatting

1
votes

I am working with excel 2010 attendance work sheet. Column B is Date, Column C is Points.
Column B has conditional formatting to highlight dates based on a revolving year, the two conditions are:

  • Formula =ISBLANK(B3)=TRUE
  • Cell Value greater than =TODAY()-365 to fill with yellow.

Column C is based on Points, the conditional format is:

  • Formula =B3>TODAY()-365 Highlight to yellow.

I need Column C the Totals Column to sum only those point numbers that are within the =B3>TODAY()-365 range. We are currently entering one cell in manually by simply counting them.

I was not able to yellow the cells in this example typed here, but if they were colored it would be 5/22/2013, 6/17/2013 and 7/30/13, as well as the corresponding numbers in the POINTS column. I do want to sum by fill color.

Joe Blow
REASON                                DATE            POINTS        TOTALS
Tardy-Work>5 hours-leave early       3/10/3012         0.5            3
With 2 week notification-unpaid      4/12/2013           1
With 2 week notification-unpaid      5/22/2013           1
Ill                                  6/17/2013           1
Tardy-Work<5 hours-report late       7/30/2013           1
1
arraysexcelexcel-2010

1 Answers

-1
votes

Please try: 

=SUMIFS(C:C,B:B,">"&TODAY()-365)