SOLVED

Microsoft

Attendance Team and manager wise

Attached the sample attendance sheet, I need the attendance list team wise daily and lead wise present data.

 NAME OF EMP Team Lead 01 02 Fri Sat Kamal Krishnan A X HOL WO Rupa Sen A X HOL WO Resham Bahadur b Y HOL WO Aman Malik b Y HOL WO Sujit panannapet C Y HOL WO sssdf A X HOL WO sdfsdfsdfsd A Y HOL WO afsdfsdfsfsd A Y HOL WO RETDFG B Y HOL WO THYTUJKI C Y HOL WO Team 01 02 03 04 A B C Lead 01 02 03 04 X Y
15 Replies

Re: Attendance Team and manager wise

Could you please clarify what shall be the result, it will be great if you add manually for one-two days. Sorry, I didn't catch what exactly is required.

Re: Attendance Team and manager wise

I need the sum of P for each date ( Team wise and Lead wise)

 NAME OF EMP Team Lead 01-Jan 02-Jan 03-Jan 04-Jan 05-Jan Fri Sat Sun Mon Tue Kamal Krishnan A X HOL WO WO P P Rupa Sen A X HOL WO WO P P sssdf A X HOL WO WO P P sdfsdfsdfsd A Y HOL WO WO P P afsdfsdfsfsd A Y HOL WO WO P P Team 01-Jan 02-Jan 03-Jan 04-Jan 05-Jan 06-Jan 07-Jan A 0 0 0 5 B 0 0 0 3 C 0 0 0 2 Lead 01-Jan 02-Jan 03-Jan 04-Jan 05-Jan 06-Jan 07-Jan X 0 0 0 3 Y 0 0 0 7

Re: Attendance Team and manager wise

If you require Counts, you could use COUNTIFS.

``````= COUNTIFS( XLOOKUP(@day, dayOfMonth, attendance), "P", Teams, Team )

Re: Attendance Team and manager wise

@Peter Bartholomew Could you please forward me the excel sheet

When I am applying the formula mentioned I am getting the below error: My email ID is v-rebaha@microsoft.com or you can upload the excel to this same post.

best response confirmed by resham1985 (Microsoft)
Solution

Re: Attendance Team and manager wise

That could be

``````=COUNTIFS(XLOOKUP(B\$15,\$D\$1:\$AH\$1,\$D\$3:\$AH\$14),"P",\$B\$3:\$B\$14,\$A16)

and

=COUNTIFS(XLOOKUP(B\$22,\$D\$1:\$AH\$1,\$D\$3:\$AH\$14),"P",\$C\$3:\$C\$14,\$A23)``````

drag them to the right and down

Re: Attendance Team and manager wise

@Sergei Baklan Thank you for your help.

Re: Attendance Team and manager wise

@resham1985 , you are welcome

Re: Attendance Team and manager wise

I didn't attach the workbook because I didn't know at that stage in the discussion that you could use the XLOOKUP function or the '@' operator.  Named Ranges have been around since Excel was first ported to DOS.

Thank you for tidying up after me!  Not fair on you though.  I once posted on the Chandoo forum that the A1 notation and the practice of copying single-cell relative references were abominations that should never have entered the world of serious computing.  I can safely say that it did not meet with overwhelming support and the spreadsheet world and I agreed to part ways.

I am holding off recommending recursive Lambdas as an alternative to the concept of relative referencing though; the latter may be a mess to define but the former is mind-numbingly contorted.  There seems to be more to support array processing in the pipeline though.

Re: Attendance Team and manager wise

@Peter Bartholomew Thank you Peter for your help.

You both quickly helped me and got to learn new things.

Re: Attendance Team and manager wise

@Peter Bartholomew , in general you are right with exhortation to use, I can't say more advanced, but more systematic style of work in Excel. But everything depends on concrete case. If people are more comfortable with with relative/absolute references - why not. On the other hand I remember the case when tried to fix an error in the file with few dozens of named ranges and formulas, and that only for author was obvious what each name means. Spent lot of time with that, I believe with usual references what could be done much faster.

Re: Attendance Team and manager wise

You have a point.  Whereas conventional spreadsheets require the reader to follow each precedent and look for adjacent annotation, names are better understood by referring to the documentation.  That begs the question of 'is there any documentation?'

I used to find reading a spreadsheet rather like reading a message written in a Caesar Cipher.  The encryption rules are simple enough but it still makes the message difficult to read.  My normal process was to decode one reference at a time and name it .  By the time I had named every reference, I most likely understood the workbook.  What that did not answer, was the question 'given a workbook built from defined names in the first place (where the naming convention might be idiosyncratic), could I do any better?  I believe the process is, in essence, to build the missing documentation.  For each name, I want to know: what business object does it represent; how it is calculated; what is its intended use; and, less importantly, where are any range references located?

I have a copy of an academic paper which asserts that names are useless as a device for reducing spreadsheet risk.  It turned out that the experiments were to test the accuracy with which students could reproduce a series of formulae such as

= PrettyPollyGrossProfit + ThirstyFerretNetProfit

My immediate thoughts were why is one company's gross profit being added to a net profit?  Why proliferate Names?  Wouldn't the structure of the data be improved by having a defined name, CompanyName, with the individual names as text data values?  One then has meaningful relationships between GrossProfit and NetProfit that apply across all companies.

All of which suggests a level of planning that is alien to spreadsheet development.  There the strategy is more one of rushing in, getting the job done and sorting the errors later.

Re: Attendance Team and manager wise

@Peter Bartholomew , you are organised person with great skills. If average Joe instead of

= PrettyPollyGrossProfit + ThirstyFerretNetProfit

will use

= Profit1 + Profit2

that won't help in understanding the logic.

Re: Attendance Team and manager wise

The attached is your problem but the solution uses new methods only available in Excel 365 beta.  The recursion required to perform the calculations for each of 31 days as a single calculation is the sort of stuff that fries ones brain so I urge you not to pay it too much attention.

I had to try to fight this one through as a single recursive Lambda function.  That it is possible is interesting, but I really struggle getting my senile brain round the challenges of recursion!  I am hoping that a future MAP function might make the application of Lambda functions to arrays much easier.  Maybe I should take some Python or Haskell training if I am going to get a grip on this.  My FORTRAN days do not stand me in good stead!

Re: Attendance Team and manager wise

@Peter Bartholomew , yes recursion with lambdas is not the easiest way to edit and debug, even if with some tricks is possible to do with in-cell edit mode.