Feb 02 2021 12:42 AM
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 |
Feb 02 2021 02:08 AM
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.
Feb 02 2021 02:19 AM
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 |
Feb 02 2021 02:26 AM
If you require Counts, you could use COUNTIFS.
= COUNTIFS( XLOOKUP(@day, dayOfMonth, attendance), "P", Teams, Team )
= COUNTIFS( XLOOKUP(@day, dayOfMonth, attendance), "P", Leads, Lead )
Feb 02 2021 02:35 AM
@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.
Feb 02 2021 02:43 AM
SolutionThat 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
Feb 02 2021 04:44 AM - edited Feb 02 2021 04:45 AM
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.
Feb 02 2021 04:53 AM
@Peter Bartholomew Thank you Peter for your help.
You both quickly helped me and got to learn new things.
Feb 02 2021 05:22 AM
@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.
Feb 02 2021 07:51 AM
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.
Feb 02 2021 08:07 AM
@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.
Feb 02 2021 02:20 PM
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!
Feb 04 2021 06:12 AM
@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.
Feb 02 2021 02:43 AM
SolutionThat 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