need help to consolidate the attendance in excel

[rensisam]

Hi all

This is my attendance file. I have manually calculated and consolidated the absentees (C11:J11).

 I want to calculate this automatically.

Pl help

Rensi

[#2]

@rensisam 

See the attached version.

[#3]

Thankyou @Hans Vogelaar .

But I am not able to reproduce the formula (=_xlfn.TEXTJOIN(",",TRUE,IF(H$3:H$61="Ab",$B$3:$B$61,""))) Is it because I am working on Excel 2007 version?

When I add a new column and repeat the same , it shows "#Name?".

Sorry, I am not that familiar with Excel (could you share some help/document to explain this formula).

Rensi

[#4]

@rensisam 

TEXTJOIN is only available in Excel 2019 and 365, so it won't work in Excel 2007.

Because of that, you will have to use a custom VBA function.

The attached version is now a macro-enabled workbook, so you will have to enable macros when you open it.