I am trying to track support hours in a shared document and want to have the entire month turn red if the number of support hours allocated in the month exceed the client's allowance. The date of a task is entered on a row, along with a description of the task and the number of support hours used.

I am using the formula =IFERROR(SUM(FILTER(C2:C997,MONTH(A2:A997)=2)),0) to find the total number of hours used in a given month (we'll do a new one each year), and I want all rows with tasks created during that month to turn red if this total exceeds 50.

Date of task Task information Hours used
1 1/1/24 A task 20
2 22/1/24 Another task 25
3 30/1/24 A third task 30
4 2/2/24 A further task 15
5 4/2/24 Yet another task 7
6 15/2/24 Even more tasks 18
7 29/2/24 A leap task 9

In the example, the tasks assigned in January total 75 hours, so rows 1, 2 and 3 should be highlighted in red. However, the tasks assigned in February only total 49, so those tasks in rows 4-7 should remain unaffected.

In the table I'm working on I've extracted the values of 75 and 49 to individual cells so I can conditionally format based on the value of those cells, but I'm struggling to find out how to define the range of the affected rows.

1 Answer 1

=50 <= SUMPRODUCT($C$2:$C, EOMONTH($A$2:$A,)=EOMONTH($A2,))
  1. Compares Month-Year, not simply month.
  2. Assumes first cell in range to be conditionally formatted is A2
  • 1
    Thanks for that, works great - I was hoping to use the same formula to output the total hours used each month in a separate table but it only returns the first month of data, are you able to assist with that also or would this be a separate question? My table works but only for the month, not the year, which is why I ask Commented Mar 6 at 16:46
  • Ask a new question that includes the formula that isn't working and I'll be happy to help if others don't get there first.
    – Blindspots
    Commented Mar 6 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.