Count only one occurrence of all criteria being met

ott3rpop

New Member
Joined
Feb 28, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I am trying to find a formula that will only count one or the first occurrence of when all the criteria I need are met.

I have a formula that will determine all instances that all criteria are met. Basically count how many times an employee appears in the list when the date is within this week, the status is complete, and the work done was a swap. This gives me the total jobs they completed this week.

=COUNTIFS(B:B,"*"&F2&"*",C:C,"SWAP",A:A,">="&TODAY()-WEEKDAY(TODAY(),3),A:A,"<"&TODAY()-WEEKDAY(TODAY(),3)+7,D:D,"COMPLETE")

I also want to find out how many days this week the employee appears in the list, with a complete status, and the work done was a swap. More than one job can be completed in a day, and more than one person can be assigned to a job.

My end goal is, I need to know how many days each employee worked so I can determine if they are working to full capacity for the week. For example, if an employee can complete 5 jobs a day and was scheduled for 5 days, they should have completed 25 jobs this week. Then I can compare that to how many they actually completed to determine if they are at, above, or below capacity. And eventually I want to do the same for YTD. So technically the "FIRST INSTANCE" column on my spreadsheet should be labeled "NUMBER OF DAYS WORKED".

The reason I need all the criteria to be met to determine how many days they worked, and not just the first time their name appears on a date is because if the status isn't complete, they may have been scheduled, but didn't work. And I need to track all this information as Swaps vs Cuts.

The first formula I am able to replace with a pivot table, but the issue I run into for tracking how many days a person worked is that more than one person is assigned to a job, and I don't know how to separate it by individual name, as a pivot table would only create 3 rows based on the current data.

Mark, Bob, Henry
Mark, Henry
Bob, Mark

But the rows I would need for a pivot table to work would be...

Mark
Bob
Henry

I also tried using a formula to separate the Employee column, so there is only one name per column, but I can't get that to work within a pivot table either. So the only thing I can think of is by doing it with a formula somehow, but I have tried lots of different formulas and can't figure out how to only count once per day this week count and employee if all other criteria are met.

This is just a sample spreadsheet I made to get the basic info accross. The actual spreadsheet is updated daily with a report pulled from different software, so the info in columns A:E change every day as well.

If there is any other information I should have included or didn't explain well, please ask, I will answer anything I can.
 

Attachments

  • Screenshot 2024-02-28 144345.png
    Screenshot 2024-02-28 144345.png
    88.7 KB · Views: 22

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Your description is confusing. You state that you want to count the first occurrence when all criteria are met (that would be a count of "one"...no counting is needed). Then you state that you have a formula that will determine all instances where all criteria are met. That's different.
Basically count how many times an employee appears in the list when the date is within this week, the status is complete, and the work done was a swap. This gives me the total jobs they completed this week.
...and then this:
I also want to find out how many days this week the employee appears in the list, with a complete status, and the work done was a swap. More than one job can be completed in a day, and more than one person can be assigned to a job.
Aren't both of these sentences saying the same thing?
So technically the "FIRST INSTANCE" column on my spreadsheet should be labeled "NUMBER OF DAYS WORKED".
This needs to be explained better. I am not sure about what you want, but I do understand the dilemma created by receiving data with employee names concatenated in a single field. You have suggested trying to split the names into separate columns, and I think that is a step in the right direction...especially if you have plans for more analysis of the data set. I would go further and unpivot the names to create a long, flat table that is well suited for counting operations. Normally, I would first consider using either Power Query or VBA to do the transformation, but you can achieve very similar results using formulas in Excel 365.

In the example below, I've left some intermediate results visible so that you can see what different parts of the formula do. The G2# formula splits out the names into a horizontal array for each row. The K2# formula takes the other data columns of interest--that are not to be unpivoted--and repeats them so that they will match up correctly with the names...after the names are stacked into a single column.

All of this is done in the P2# formula (so you don't need the G2 or K2 formulas...just delete them), which creates a flat table of your original data. I've also included an additional column for the week number associated with the Date. Using WEEKNUM is a more direct way of determining which entries occurred during the same week as TODAY(), so the "wn" (week number) array can be directly used in a counting formula. As for the counting formula, COUNTIF(S) will not accept an array name for the criteria range (as the name indicates, it must be a range reference, not an array), so I've shown an alternative approach using SUMPRODUCT. Depending on how long your flat table might become, the formula could become sluggish at very large sizes.

I do not know what you want for the "NUMBER OF DAYS WORKED" column. The data are in a more accessible format, but it's not clear what criteria should be applied. In the example below, I've truncated the worksheet snippet a few rows below your original table length due to size limitations. The formula in this example grows to three times the original length because there are three columns of employee names split out from the original data. What is the maximum number of names that might appear in the EMPLOYEE field on any given day? And approximately how many rows are your data tables as received?
MrExcel_20240229.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1JOBDATEEMPLOYEESWAP/CUTStatusJOBDATESWAP/CUTStatusWEEKNUMEMPLOYEEEMPLOYEEALL INSTANCESNUMBER OF DAYS WORKED
212342/25/2024Mark, Bob, HenrySWAPCOMPLETEMarkBobHenry123445347SWAPCOMPLETE123445347SWAPCOMPLETE8MarkMark8
312352/25/2024Mark, HenryCUTCOMPLETEMarkHenry123545347CUTCOMPLETE123545347CUTCOMPLETE8MarkBob5
412362/26/2024Bob, MarkSWAPCOMPLETEBobMark123645348SWAPCOMPLETE123645348SWAPCOMPLETE9BobHenry5
512372/26/2024Mark, Bob, HenrySWAPCOMPLETEMarkBobHenry123745348SWAPCOMPLETE123745348SWAPCOMPLETE9Mark
612382/27/2024Mark, HenryCUTCOMPLETEMarkHenry123845349CUTCOMPLETE123845349CUTCOMPLETE9Mark
712392/27/2024Bob, MarkSWAPCOMPLETEBobMark123945349SWAPCOMPLETE123945349SWAPCOMPLETE9Bob
812402/28/2024Mark, Bob, HenryCUTCOMPLETEMarkBobHenry124045350CUTCOMPLETE124045350CUTCOMPLETE9Mark
912412/28/2024Mark, HenrySWAPCOMPLETEMarkHenry124145350SWAPCOMPLETE124145350SWAPCOMPLETE9Mark
1012421/5/2024Bob, MarkSWAPCOMPLETEBobMark124245296SWAPCOMPLETE124245296SWAPCOMPLETE1Bob
1112431/5/2024Mark, Bob, HenryCUTCOMPLETEMarkBobHenry124345296CUTCOMPLETE124345296CUTCOMPLETE1Mark
1212441/6/2024Mark, HenrySWAPCOMPLETEMarkHenry124445297SWAPCOMPLETE124445297SWAPCOMPLETE1Mark
1312451/6/2024Bob, MarkCUTCOMPLETEBobMark124545297CUTCOMPLETE124545297CUTCOMPLETE1Bob
1412461/7/2024Mark, Bob, HenrySWAPCOMPLETEMarkBobHenry124645298SWAPCOMPLETE124645298SWAPCOMPLETE1Mark
1512471/7/2024Mark, HenrySWAPCOMPLETEMarkHenry124745298SWAPCOMPLETE124745298SWAPCOMPLETE1Mark
1612481/8/2024Bob, MarkCUTCOMPLETEBobMark124845299CUTCOMPLETE124845299CUTCOMPLETE2Bob
1712491/8/2024Mark, Bob, HenrySWAPCOMPLETEMarkBobHenry124945299SWAPCOMPLETE124945299SWAPCOMPLETE2Mark
1812501/9/2024Mark, HenryCUTCOMPLETEMarkHenry125045300CUTCOMPLETE125045300CUTCOMPLETE2Mark
1912511/9/2024Bob, MarkSWAPCOMPLETEBobMark125145300SWAPCOMPLETE125145300SWAPCOMPLETE2Bob
2012521/10/2024Mark, Bob, HenrySWAPCOMPLETEMarkBobHenry125245301SWAPCOMPLETE125245301SWAPCOMPLETE2Mark
2112531/10/2024Mark, HenryCUTCOMPLETEMarkHenry125345301CUTCOMPLETE125345301CUTCOMPLETE2Mark
2212542/25/2024Bob, MarkSWAPCOMPLETEBobMark125445347SWAPCOMPLETE125445347SWAPCOMPLETE8Bob
2312552/25/2024Mark, Bob, HenryCUTCOMPLETEMarkBobHenry125545347CUTCOMPLETE125545347CUTCOMPLETE8Mark
2412562/26/2024Mark, HenrySWAPCOMPLETEMarkHenry125645348SWAPCOMPLETE125645348SWAPCOMPLETE9Mark
2512572/26/2024Bob, MarkSWAPCOMPLETEBobMark125745348SWAPCOMPLETE125745348SWAPCOMPLETE9Bob
2612582/27/2024Mark, Bob, HenryCUTCOMPLETEMarkBobHenry125845349CUTCOMPLETE125845349CUTCOMPLETE9Mark
2712592/27/2024Mark, HenrySWAPCOMPLETEMarkHenry125945349SWAPCOMPLETE125945349SWAPCOMPLETE9Mark
2812602/28/2024Bob, MarkCUTCOMPLETEBobMark126045350CUTCOMPLETE126045350CUTCOMPLETE9Bob
2912612/28/2024Mark, Bob, HenrySWAPCOMPLETEMarkBobHenry126145350SWAPCOMPLETE126145350SWAPCOMPLETE9Mark
3012621/5/2024Mark, HenrySWAPCOMPLETEMarkHenry126245296SWAPCOMPLETE126245296SWAPCOMPLETE1Mark
3112631/5/2024Bob, MarkCUTCOMPLETEBobMark126345296CUTCOMPLETE126345296CUTCOMPLETE1Bob
3212641/6/2024Mark, Bob, HenrySWAPCOMPLETEMarkBobHenry126445297SWAPCOMPLETE126445297SWAPCOMPLETE1Mark
3312651/6/2024Mark, HenryCUTCOMPLETEMarkHenry126545297CUTCOMPLETE126545297CUTCOMPLETE1Mark
3412661/7/2024Bob, MarkSWAPCOMPLETEBobMark126645298SWAPCOMPLETE126645298SWAPCOMPLETE1Bob
3512671/7/2024Mark, Bob, HenrySWAPCOMPLETEMarkBobHenry126745298SWAPCOMPLETE126745298SWAPCOMPLETE1Mark
3612581/8/2024Mark, HenryCUTCOMPLETEMarkHenry125845299CUTCOMPLETE125845299CUTCOMPLETE2Mark
37123445347SWAPCOMPLETE123445347SWAPCOMPLETE8Bob
38123545347CUTCOMPLETE123545347CUTCOMPLETE8Henry
39123645348SWAPCOMPLETE123645348SWAPCOMPLETE9Mark
40123745348SWAPCOMPLETE123745348SWAPCOMPLETE9Bob
41123845349CUTCOMPLETE123845349CUTCOMPLETE9Henry
Sheet1
Cell Formulas
RangeFormula
G2:I36G2=DROP(IFERROR(REDUCE(0,$C$2:$C$36,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,", ")))),""),1)
K2:N106K2=LET(nemp,COLUMNS(G2#),data,CHOOSECOLS($A$2:$E$36,1,2,4,5),rdata,ROWS(data),res,REDUCE(data,SEQUENCE(nemp-1),LAMBDA(a,b,VSTACK(a,data))),res)
P2:U106P2=LET(source,$A:$E, lrow,MATCH(1E+100,$A:$A), data, DROP(TAKE(source,lrow),1), emp, DROP(IFERROR(REDUCE(0,CHOOSECOLS(data,3),LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,", ")))),""),1), nemp,COLUMNS(emp), nndata,HSTACK(CHOOSECOLS(data,1,2,4,5),WEEKNUM(CHOOSECOLS(data,2),2)),rdata,ROWS(data),nndata_stacked,REDUCE(nndata,SEQUENCE(nemp-1),LAMBDA(a,b,VSTACK(a,nndata))),HSTACK(nndata_stacked,TOCOL(emp,,TRUE)))
W2:W4W2=UNIQUE(TOCOL(DROP(REDUCE(0,$C$2:$C$36,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,", ")))),1),3))
X2:X4X2=LET(data,$P$2#,sc,CHOOSECOLS(data,3),st,CHOOSECOLS(data,4),wn,CHOOSECOLS(data,5),emp, CHOOSECOLS(data,6), resALL, SUMPRODUCT( (wn=WEEKNUM(TODAY(),2)) * (emp=W2) * (sc="SWAP") * (st="COMPLETE") ), resALL)
Dynamic array formulas.
 
Upvote 0
Your description is confusing. You state that you want to count the first occurrence when all criteria are met (that would be a count of "one"...no counting is needed). Then you state that you have a formula that will determine all instances where all criteria are met. That's diff
Yes, the formula I have is different from what I am asking for help with. I also need this formula. I need to know both sets of information; all instances that criteria are met within a week per employee and how many days this week all criteria are met per employee. I included this to show what I have so far incase it can be altered to accomplish the second parameters.

Aren't both of these sentences saying the same thing?
They aren't the same. The first thing I need, and the formula I do have, tells me all completed jobs in a week per employee, that number could be anywhere from zero to 30+. I also need to know how many days this week an employee completed a job, that number can't be higher than 5 (working M-F, a total of 5 days potentially).

This needs to be explained better. I am not sure about what you want
My end goal is to determine how many days this week an employee worked. So if I can count only the first instance per day this week that all criteria are met, that would get me the number of days they worked. That's what I don't know how to do, I don't know how to only count one instance per day. I've only been able to figure out how to count all instances within a week.

You have suggested trying to split the names into separate columns, and I think that is a step in the right direction
I tried this, but I still couldn't figure out a formula to only count the first (or one) instance per day.

I also want to avoid adding a significant amount of new columns with formulas to my spreadsheet. The actual spreadsheet is very large, adding a lot more to it causes it to be bogged down and unusable unfortunately. I could potentially add a column to calculate the week number. Would that change my formula from this...
=COUNTIFS(B:B,"*"&F2&"*",C:C,"SWAP",A:A,">="&TODAY()-WEEKDAY(TODAY(),3),A:A,"<"&TODAY()-WEEKDAY(TODAY(),3)+7,D:D,"COMPLETE")
to...
=COUNTIFS(B:B,"*"&F2&"*",C:C,"SWAP",F:F,=WEEKNUM(TODAY()),D:D,"COMPLETE")

Assuming F:F would be my new column with the week number?
 
Upvote 0
Thanks for the clarification...I think I understand now. So the distinction is that one is a count of the number of jobs during the week, the other is a count of the number of days during the week...and both counts require other criteria to be satisfied. Regarding your comment about adding new columns, what I posted was intended only to show what the formula is doing internally...it creates an internal array that can be used to deliver the results needed without displaying anything other than the results...no additional columns would be needed. Below is an example where the formulas presented have been consolidated and slightly revised to deliver what you want.

I'll attempt to step through the formula to describe the various parts: The source data is defined with whole column references, which seems to be your preference. Next, the last row (lrow) of the source data is determined using column A. Then a multi-column array called "data" is defined by trimming down the size of the "source" so that it includes only the rows where data are found. Assuming the employee names are found in column 3 of "source", we split apart multiple names in the Employee Names column using a comma space ", " as the delimiter. This creates a two-dimensional array with names appearing across the columns for each row. The number of columns of this two-dimensional array is determined ("nemp"). Because so much of the analysis depends on knowing if something occurred during the same week, it is convenient to know the week number, so an array of week numbers associated with each date in "data" is created and appended (horizontally) to columns 1,2,4, and 5 of data (Job, Date, Swap/Cut, and Status), and this array is called "nndata" to reflect that it is based on the original "data" but no names are included. This "nndata" array needs to be stacked onto itself multiple times and then aligned with the unpivoted names. To do this, a new variable called "nndata_stacked" is defined using a LAMBDA function to repeatedly stack "nndata" based on the number of columns in the split-names array ("nemp"). Finally, this vertically stacked array is combined (horizontally) with the employee names that were previously split apart...all of those names are stacked vertically, column-by-column to align correctly with "nndata_stacked". This new array is "flat" and very convenient for subsequent use in determining the quantities of interest. The new data array is called "fdata" to indicate is it flat.

For convenience, the various columns of "fdata" are assigned to more meaningful variable names: "dt" for dates, "sc" for swap/cut, "st" for status, "wn" for week number, and "emp" for employee name. Then the counts desired are given using a FILTER function to extract all of the date ("dt") entries where the criterial are met. Where a total number of assignments is desired, the number of dates is counted ("resAll" for results All). Where the total number of days is desired, the UNIQUE function is used to include only unique dates, and those are counted ("resNdays" for results Number of days). Both results are stacked horizontally and delivered as one array.
MrExcel_20240229.xlsx
ABCDEFGHI
1JOBDATEEMPLOYEESWAP/CUTStatusEMPLOYEEALL INSTANCESNUMBER OF DAYS WORKED
212342/25/2024Mark, Bob, HenrySWAPCOMPLETEMark83
312352/25/2024Mark, HenryCUTCOMPLETEBob53
412362/26/2024Bob, MarkSWAPCOMPLETEHenry53
512372/26/2024Mark, Bob, HenrySWAPCOMPLETE
612382/27/2024Mark, HenryCUTCOMPLETE
712392/27/2024Bob, MarkSWAPCOMPLETE
812402/28/2024Mark, Bob, HenryCUTCOMPLETE
912412/28/2024Mark, HenrySWAPCOMPLETE
1012421/5/2024Bob, MarkSWAPCOMPLETE
1112431/5/2024Mark, Bob, HenryCUTCOMPLETE
1212441/6/2024Mark, HenrySWAPCOMPLETE
1312451/6/2024Bob, MarkCUTCOMPLETE
1412461/7/2024Mark, Bob, HenrySWAPCOMPLETE
1512471/7/2024Mark, HenrySWAPCOMPLETE
1612481/8/2024Bob, MarkCUTCOMPLETE
1712491/8/2024Mark, Bob, HenrySWAPCOMPLETE
1812501/9/2024Mark, HenryCUTCOMPLETE
1912511/9/2024Bob, MarkSWAPCOMPLETE
2012521/10/2024Mark, Bob, HenrySWAPCOMPLETE
2112531/10/2024Mark, HenryCUTCOMPLETE
2212542/25/2024Bob, MarkSWAPCOMPLETE
2312552/25/2024Mark, Bob, HenryCUTCOMPLETE
2412562/26/2024Mark, HenrySWAPCOMPLETE
2512572/26/2024Bob, MarkSWAPCOMPLETE
2612582/27/2024Mark, Bob, HenryCUTCOMPLETE
2712592/27/2024Mark, HenrySWAPCOMPLETE
2812602/28/2024Bob, MarkCUTCOMPLETE
2912612/28/2024Mark, Bob, HenrySWAPCOMPLETE
3012621/5/2024Mark, HenrySWAPCOMPLETE
3112631/5/2024Bob, MarkCUTCOMPLETE
3212641/6/2024Mark, Bob, HenrySWAPCOMPLETE
3312651/6/2024Mark, HenryCUTCOMPLETE
3412661/7/2024Bob, MarkSWAPCOMPLETE
3512671/7/2024Mark, Bob, HenrySWAPCOMPLETE
3612581/8/2024Mark, HenryCUTCOMPLETE
Sheet1
Cell Formulas
RangeFormula
G2:G4G2=LET(source,$C:$C, lrow,MATCH(1E+100,$A:$A), data,DROP(TAKE(source,lrow),1),UNIQUE(TOCOL(DROP(REDUCE(0,data,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,", ")))),1),3)) )
H2:I4H2=LET(source,$A:$E, lrow,MATCH(1E+100,$A:$A), data,DROP(TAKE(source,lrow),1), empsrc, DROP(IFERROR(REDUCE(0,CHOOSECOLS(data,3),LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,", ")))),""),1), nemp,COLUMNS(empsrc), nndata,HSTACK(CHOOSECOLS(data,1,2,4,5),WEEKNUM(CHOOSECOLS(data,2),2)), nndata_stacked,REDUCE(nndata,SEQUENCE(nemp-1),LAMBDA(a,b,VSTACK(a,nndata))), fdata, HSTACK(nndata_stacked,TOCOL(empsrc,,TRUE)), dt,CHOOSECOLS(fdata,2), sc,CHOOSECOLS(fdata,3), st,CHOOSECOLS(fdata,4), wn,CHOOSECOLS(fdata,5), emp, CHOOSECOLS(fdata,6), resALL, COUNTA(FILTER(dt,(wn=WEEKNUM(TODAY(),2)) * (emp=G2) * (sc="SWAP") * (st="COMPLETE") )), resNdays, COUNTA(UNIQUE(FILTER(dt,(wn=WEEKNUM(TODAY(),2)) * (emp=G2) * (sc="SWAP") * (st="COMPLETE") ))), HSTACK(resALL,resNdays))
Dynamic array formulas.
 
Upvote 0
I finally had time today to work on this. Your formulas are so far out of my excel abilities I am not even sure I understand your explanations! This is WAY more advanced than I am currently capable of. That being said, I would love to try it out with my spreadsheet and see if I can get it to work!
=LET(source,$C:$C, lrow,MATCH(1E+100,$A:$A), data,DROP(TAKE(source,lrow),1),UNIQUE(TOCOL(DROP(REDUCE(0,data,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,", ")))),1),3)) )
Do I need to change anything within this formula other than $C:$C and $A:$A to customize it to fit my actual spreadsheet? Or are there other variables within the formula that I need to change to match my data set? In my spreadsheet $C:$C would be $CX:$CX. And $A:$A would be $B:$B.

Once I get this part of it working for me I will ask how to customize the second formula. Baby steps since this is so far out of my wheelhouse.
 
Upvote 0
Yes...your plans for adapting the formula sound correct. This formula does nothing more than create a list of employees. If you'd like, that list could also appear sorted (the formula doesn't do that as written, but adding that step would be easy). The only "hooks" into your sheet are those two column specifications. Column C/CX would list the names, perhaps in some cases separated by a "comma space". You'll see that delimiter in the formula near the end. If a different delimiter is used in your actual file, or if the delimiter is a comma and no space, or a comma and two spaces, then either some data clean up might be needed to make everything consistent, or change the delimiter in the formula. Regarding the column A/(or B in your latest case), the formula is assuming that column contains numbers, not text. The idea is that we use the MATCH function to look down the column for a ridiculously large number that doesn't actually exist in the column, and MATCH will return the last row number...and that is used to limit the size of the data array used in the formula without having to hardwire in the actual data range.

Once you have that working, post back...an additional adjustment might be needed on the other formula, as the current one assumes you'll be specifying 5 adjacent columns, and it sounds as if that may not be the case...easily fixed, but will need some details.
 
Upvote 0
Regarding the column A/(or B in your latest case), the formula is assuming that column contains numbers, not text.
My actual job numbers contain both letters and numbers, for example: L12B4C0- Swap (C22) or KB-422-A01-L2 (Swap) B40

I have no control over this, I think they auto-generated by the software. Sorry, I didn't realize the formula depended on the job numbers being only numbers. Is there a way to change to formula to make it work with non-number data?
 
Upvote 0
Yes, sure is…I’ll post back when I get to a computer.
 
Upvote 0
If there is nothing below the "A" column, which I think you said would be your "B" column...we could change up the way the last row number is found by using this revised "lrow" formula (see lrow on the first line). This type of formula evaluates whether the A column cells are not blank. Blank cells will evaluate as FALSE (A<>""), while cells with something in them (text or number...doesn't matter) will evaluate as TRUE. We divide the array of TRUE/FALSE into 1, which coerces TRUEs to 1's and FALSE;s to 0's...and the array will consist of 1's and #DIV/0! errors. LOOKUP then searches this array for the number 2 (which doesn't exist), so it's default behavior is to find the last non-error position, and we return the ROW number for that position. We don't have to use column A (or B) for this last row determination...any column will work, provided it's last cell corresponding to the bottom of the table contains data.
Excel Formula:
=LET(source,$A:$E, lrow,LOOKUP(2,1/($A:$A<>""),ROW($A:$A)),
data,DROP(TAKE(source,lrow),1),
empsrc, DROP(IFERROR(REDUCE(0,CHOOSECOLS(data,3),LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,", ")))),""),1),
nemp,COLUMNS(empsrc),
nndata,HSTACK(CHOOSECOLS(data,1,2,4,5),WEEKNUM(CHOOSECOLS(data,2),2)),
nndata_stacked,REDUCE(nndata,SEQUENCE(nemp-1),LAMBDA(a,b,VSTACK(a,nndata))),
fdata, HSTACK(nndata_stacked,TOCOL(empsrc,,TRUE)),
dt,CHOOSECOLS(fdata,2),  sc,CHOOSECOLS(fdata,3),  st,CHOOSECOLS(fdata,4),  wn,CHOOSECOLS(fdata,5),  emp, CHOOSECOLS(fdata,6),
resALL,      COUNTA(FILTER(dt,(wn=WEEKNUM(TODAY(),2)) * (emp=G2) * (sc="SWAP") * (st="COMPLETE") )),
resNdays, COUNTA(UNIQUE(FILTER(dt,(wn=WEEKNUM(TODAY(),2)) * (emp=G2) * (sc="SWAP") * (st="COMPLETE") ))),
HSTACK(resALL,resNdays))
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top