I'm glad the formula helped you out. I know the formula looks daunting but it really isn't very complicated. I will separate it by dashes to better explain it. (Also note that this formula was not available on Excel versions before Excel 2007.)
=COUNTIFS($C:$C, $C3, ---------------- $B:$B, $B3,---------------- $D:$D, ">="&$D3-7, ------------------ $D:$D, "<"&$D3+7) --------- >1
The COUNTIFS formula returns a count based upon multiple criteria. In the formula, you will have multiple sets of criteria with the criteria range listed first, then a comma and then the actual criteria. Each set of criteria is separated by a comma also.
In the formula above, the first criteria is $C:$C, $C3 which is looks at everything in column C ($C:$C) that equals whatever is in cell $C3. (In this case, it's the ID#)
The next criteria is $B:$B, $B3 which looks at everything in column B ($B:$B) that equals whatever is in cell $B3. (In this case, it';s the Service Type).
The next 2 criteria are looking at the date range. The first one of those is $D:$D, ">="&$D3-7 which looks at column D ($D:$D) and counts anything that is greater than or equal to 7 days less than the date in cell D3. (July 15th is in cell D3 so the count is anything greater than or equal to July 8th.)
The last of the COUNTIF criteria is $D:$D, "<"&$D3+7 which looks at column D ($D:$D) and counts anything that is less than 7 days greater than the date in cell $D3. (July 15th is in cell D3 so the count is anything less than July 22nd.)
Keep in mind, for a count to register, all of the 4 criteria must be met.
The last part of the formula is >1. (This is not part of the COUNTIFS formula but rather is just comparing the results of the COUNTIFS formula.) This simply means if the count returned by the COUNTIFS formula is greater than 1 then the conditional formatting will apply. (Which I assume you already know since you are working with conditional formatting already.) So, in your example, row 3 and row 8 would return counts of 2 and therefore would qualify for the conditional formatting. All other rows would return a count of 1 and not qualify for the conditional formatting.
I'm sure you are aware, but the $ character in the formula is just there to "lock down" the cell/column/row references so that when they are copied to other cells/columns/rows, the row & column numbers & letters will or won't change accordingly. When I see a formula with the $, I associate it with the word "always". So, $D3 would mean always column D but not always row 3. If the formula is copied across columns, the column won't change but if it is copied to other rows the row number will change.
And lastly, don't get discouraged by not receiving a response right away. MrExcel is one of the best forums I have ever used. Occasionally, I don't get a response but more often than not, you will find someone that can help. It has helped me countless times and some of the folks here are extremely knowledgeable. What I find that helps the most in getting a response is to be as clear as possible in your question as well as posting an example. (As you did) I'm sure you will have a better experience next time.
Good Luck!
