Hey guys!

So i have two sheets. The first sheet has data of all candidates that have taken different sessions, and data about the attendance on those sessions. What I need is a formula that counts the nonblank cells in a column, dependent on the label of that row.

For example Sheet1 looks like this:

SessionTitle  SessionCode  Attendance 
Session1              T1              YES
Session1              T1              
Session1              T1              
Session1              T1              YES
Session2              T2              YES
Session2              T2
Session3              T3              YES
Sheet2 should look like this:

SessionTitle  SessionCode  Attendance 
Session1              T1              2
Session2              T2              1
Session3              T3              1
Thus I need a formula for cells in the second sheet that counts the nonblank cells in the 'Attendance' column.

I managed to figure out a formula that summed up the values in another column, dependent on the sessioncode, that formula looked like this:

{=SUM(IF('Sheet1'!$B:$B='Sheet2'!$B2, 'Sheet1'!$D:$D))}

thus i thought a counta formula should work:

{=CountA(IF('Sheet1'!$B:$B='Sheet2'!$B2, 'Sheet1'!$C:$C))}

But it doesn't, it just counts everything. What am I doing wrong?