Forum Discussion
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 |
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
15 Replies
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!
PeterBartholomew1 , 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.
If you require Counts, you could use COUNTIFS.
= COUNTIFS( XLOOKUP(@day, dayOfMonth, attendance), "P", Teams, Team ) = COUNTIFS( XLOOKUP(@day, dayOfMonth, attendance), "P", Leads, Lead )PeterBartholomew1 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.
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.
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.
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
