SOLVED

Attendance Team and manager wise

Microsoft

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

@resham1985 

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.

@Sergei Baklan 

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      

@resham1985 

image.png

If you require Counts, you could use COUNTIFS.

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

= COUNTIFS( XLOOKUP(@day, dayOfMonth, attendance), "P", Leads, Lead )

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

resham1985_0-1612262062623.png

 

best response confirmed by resham1985 (Microsoft)
Solution

@resham1985 

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

@Sergei Baklan Thank you for your help.

@resham1985 , you are welcome

@resham1985 

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.

@Sergei Baklan 

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.

@Peter Bartholomew Thank you Peter for your help.

 

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

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

 

 

@Sergei Baklan 

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.

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

@resham1985 

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.

 

@Sergei Baklan 

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!

 

image.png

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

1 best response

Accepted Solutions
best response confirmed by resham1985 (Microsoft)
Solution

@resham1985 

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

View solution in original post