If, OR and COUNTIF formula

[creed1101]

Hello

I am designing a register. The columns required are an ID, whether the ID has been verified, did the person attend? and the number of sessions attended.
The problem I'm having relates to the no of sessions attended. Currently this will return 2 for someone who didn't attend their first session and did their second session. I use a countif based on the number of times the ID appear but I think I need to incorporate another IF statement.

I have attached a spreadsheet showing the issue I'm having.

Many thanks

Nik

[NBVC]

The logic doesn't seem to correspond to the headers in the sheet...

but perhaps try in G2:

=IF(OR(E2="",E2="No",F2="Yes"),"",SUMPRODUCT(--(D$1:D2=D2),--(F$1:F2="No")))

copied down.