Formula To Count Absences Based on Criteria & Matching Dates

[LOK25]

Hi forum,

Thanks in advance for any assistance. I don't believe this is a particularly difficult formula, but i hit a roadblock.

Basically, I am trying to use a formula to calculate the # of times a person is absent from class based upon hours type and code (2 conditions).

If hours type (Column C) begins with anything BUT "R" it should count as an absence. You will notice 3 absences in Column C (LA, LS, OS) on 2/1, 2/4 and 2/10.

Next, i am trying to also count an absence for any hours type that begins with "R" and the Code (Column D) begins with "T". There are 2 of these occurrences on 2/2 and 2/8.

So i am manually counting 5 absences, but you will notice on 2/4/2021, Excel is counting the 2 occurrences (RF with TS code, and the LS) on this date twice resulting in 6 absences instead of 5.

I believe im neglecting some check of dates for duplications or matches/not matches etc.? I am really struggling with referencing the dates and creating rules based on the date as well.

Any resolution would be appreciated!