Forum Discussion
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
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.
PeterBartholomew1 , 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.
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
SergeiBaklan Thank you for your help.
