Conditional format formula when looking at the sum of multiple entries

Hi, so I am looking for a function to incorporate into a leave of absence spreadsheet that another department uses. What I want the function to do is color a specific cell - we'll just say the person's name if the amount of time they have been out on leave exceeds a certain amount of time. Now here's where it gets tricky. First off, it is entirely possible that someone might go on multiple leaves in their anniversary year. Let's say someone has a January anniversary and they go out on leave in February and are out for 60 days. That would be one row on the spreadsheet for them containing their leave date and then their return date. Let's say they then go on another leave later in the year for 40 days. That would be another row for that same person again showing that leave date and then the return date. Basically the cell where their name is located should light up a color if the combined amount of days from both leaves exceed 90. This is applicable for all leaves except Worker's Compensation. If they are out on Worker's Compensation (W/C) it should be 6 months. Basically the setup of cells is as follows:

	A	B	C	D	E
1	Name	Leave type	Leave start date	Return to work Date	Amount of days out
2	John	A/S	2/1/21	4/2/21	60
3	John	A/S	8/1/21	9/10/21	40

So in this case, John would light up because his total amount of days out would be 100 and he is NOT on a W/C leave so it would be if it's greater than 90 days. This log would contain multiple different employee names, so the same should be applicable to them. Is it possible to do this?

Try these 2:

=SUMIFS($E$2:$E$5,$A$2:$A$5,$A2,$B$2:$B$5,"<>W/C")>90
=SUMIFS($E$2:$E$5,$A$2:$A$5,$A2,$B$2:$B$5,"W/C")>183

See attached.

Thank you Greg, I showed this to the department and it's exactly what they are looking for. I will mark this as solved although I will be expanding on it in the very near future. Thankfully then, I will have the actual document they are using when they clean it up some instead of having to make my own. I will refer to this post in the future post if I am unable to come up with the solutions to whatever they want to add. Thanks again!

So I actually have another question on this one, if we add column F and call it a "Pro-ration Done?" column, they will put a "Y" there to know that a pro-ration has been done since the cell lit up to flag them of this. How complicated would it be to have the cells not color in column A if the corresponding values in column F have a "Y" in them? Is that a drastic change to the formula? Would it be a separate formula in Conditional formatting?

You could change the formula like this:

=AND(SUMIFS($E$2:$E$5,$A$2:$A$5,$A2,$B$2:$B$5,"W/C")>183,F2<>"Y")

(And similarly the other formula).

And they threw another wrench at me This time they are asking if it can be altered to allow multiple years in the same sheet. I am attaching the tab from the actual sheet to make this less confusing since they did email me the file now to use instead of having to make up my own. Bear in mind, formulas will be different than those given above as they decided to move or eliminate certain columns. I apologize for that, they are still kinda trying to iron out the kinks of where they actually want things to be, so I'm kinda working around that. We have additional columns coming into play here, specifically columns D and E. Those columns are a window of pro-ration for employees. You will see as well in column P, I have a formula to capture how many days an employee has taken off within that pro-rated period and column Q has a "Y" if they are pro-rated. This is what is tying into the conditional formatting appearing in column C. We had to switch it to employee ID since some employees have the same name. Anyways, in the attached, if we look at a specific examples: John H who has data in rows 22 and 23

1. John has an anniversary window of 10/29 so he can actually have TWO pro-ration windows - one that runs from 10/29/2020 to 10/28/2021 which is his 2021 pro-ration window, and one that would run 10/29/2021 to 10/28/2022 which is his 2022 pro-ration window. (Always the year the window ends is what year it's actually for) What they've asked me to do is see if I can make the conditional formatting run so that it does not combine their pro-ration windows in the calculation. So in John's case, we will look at row 22 in columns H, I and P. H is when he began his leave of absence overall, I is when he returned, and P is calculating the difference between those dates within the pro-ration window minus 1 so it doesn't count their return to work date as a day off since they actually physically worked. In John's case, he was out of work from 6/5/21 up to 11/8/21. In the 2021 pro-ration window which runs up to 10/28/21, that is a total of 145 days out. This leave would light up yellow because his leave was A/S and 90 days or greater. It UN-highlights because we have put a "Y" in column Q22 - they did the pro-ration, they know it's been completed, nothing further needs done with that year. You can then see that he has a second pro-ration window for 2022 which again as stated above, started on 10/29/21. So if we look in column P, we will see that within that second pro-ration window of 2022, he was only out for a total of 10 days. Since that is less than 90, it should not be highlighted in column C. It is highlighting obviously because it is combing the pro-ration window numbers of both 2021 and 2022 which are greater than 90. So my question for that is can that somehow be made separate so that it will look to see the common identifier which is employee ID but also now look at the pro-ration window as well?