Extracting sub array from an array

[Brijesh]

Please refer to the attached file.

Sheet "Input Att FacAcad" record the daily attendance of the employees.

I have to produce monthly summary of number of days Present/CL/Late on the sheet "Summ Att Faculty" based on the data on the sheet "Output Att FacAcad".

I thought to extract a subarray for a particular month and employee name from the exhaustive data on the sheet "Output Att FacAcad" and then applying countif function to produce the summary. However I could not find the way to extract subarray. Please could anybody help.

 

[p45cal]

Your arrangement of data makes it very difficult to extract information (as you've discovered).
In the attached is a Sheet1 with your data arranged differently (there's a short macro to do that in there if you want to use it).
This arrangement makes it easier to extract data with formulae or, as I have done, with a Pivot Table on sheet Summ Att Faculty.

 

[bosco_yip]

Another option in formula way.

1] Adjusting is making to Row11 "Criteria of leave",

D11:G11, enter : Present, CL Late and OFF >>

and, Custom cells format : "No of days: "@

Select D11:G11, copy across right to AY11

2] In D14, formula copy across and down :

=SUMPRODUCT((TEXT('Output Att FacAcad'!$A$9:$A$463,"mmm/yyy")=D$12)*(OFFSET('Output Att FacAcad'!$C$9,0,ROWS($1:1)*3-3,455)=D$11))

Regards
Bosco