need help to consolidate the attendance in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2452924%22%20slang%3D%22en-US%22%3Eneed%20help%20to%20consolidate%20the%20attendance%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2452924%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20my%20attendance%20file.%20I%20have%20manually%20calculated%20and%20consolidated%20the%20absentees%20(C11%3AJ11).%3C%2FP%3E%3CP%3E%26nbsp%3BI%20want%20to%20calculate%20this%20automatically.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPl%20help%3C%2FP%3E%3CP%3ERensi%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2452924%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2453057%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20to%20consolidate%20the%20attendance%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2453057%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1080244%22%20target%3D%22_blank%22%3E%40rensisam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

3 Replies

@rensisam 

See the attached version.

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

@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.