I've been struggling with getting this formula to work for a while now, so I figured I'd bring it to the pros.

Background: This is a scholarship report. . These are students who failed a course for which the scholarship paid. It is intended to find out if an amount of a scholarship needs to be repaid due to lack of attendance to a class. If a student simply failed the class, no repayment is necessary. If attendance could not be verified, funds must be returned.

ID=student
Disbursed=Scholarship Paid
Course in Question=Course where attendance needs to be verified.
Passed=Total courses student passed for the term
Attended=Was the teacher able to verify attendance of course in question?
Due: Amount due if attendance not verified

Essentially, What I need for G (Actual Attendance) is...

G=D+(C, if E=Y) OR Just D if (E=N)

H (Recalculated) will simply be B-F, but...

I also need a way to consolidate each ID. I am planning to move these to a separate sheet, but I need a way to combine the information by ID. The summary will just have ID, Attended Hours, Amount Paid, Amount Due.

I have several thousand lines and some students have 4-5 lines.

Essentially, I need to combine figures by ID and also SUMIF based on a Yes or No response. I've tried a couple versions of SUMIF formulas, but I think this is just outside my personal knowledge of formula writing. Any help would be appreciated.

Thanks. Sample1.xlsx