Forum Discussion
Counting unique records with multiple condition in excel
Hi Experts,
Seeking advise on how to find unique count of records using date and multiple conditions.
My Raw data is below:
| Date | Name | Dept | Location | Status |
| 2024-02-01 | King | Finance | Canada | Inactive |
| 2024-02-01 | Diana | Sales | Canada | Active |
| 2024-02-01 | Harry | Sales | USA | Active |
| 2024-02-01 | Stuart | Sales | USA | Active |
| 2024-02-01 | Betty | Sales | Canada | Active |
| 2024-02-01 | Evan | Finance | UK | Active |
| 2024-02-01 | Steve | Finance | UK | Active |
| 2024-03-01 | King | Finance | Canada | Inactive |
| 2024-03-01 | Harry | Sales | USA | Active |
| 2024-03-01 | Stuart | Sales | USA | Active |
| 2024-03-01 | Edwin | Finance | UK | Active |
| 2024-03-01 | Laura | HR | Canada | Active |
| 2024-04-01 | Harry | Sales | USA | Active |
| 2024-04-01 | Stuart | Sales | USA | Active |
| 2024-04-01 | Larry | HR | USA | Active |
| 2024-04-01 | Laura | HR | Canada | Active |
| 2024-05-01 | Harry | Sales | USA | Active |
| 2024-05-01 | Thomas | Sales | USA | Active |
| 2024-05-01 | Stuart | Sales | USA | Active |
| 2024-05-01 | Larry | HR | USA | Active |
| 2024-05-01 | Laura | HR | Canada | Active |
| 2024-05-01 | Betty | Sales | Canada | Active |
My output which I am expecting is below.
My output which I am expecting is below.
The user shall enter the From and To dates and the Status=Active/Inactive. The output should be the count of active distinct employees during that period by Department.
Appreciate your help in advance.
Regards,
PPG
4 Replies
365 variant:
=LET( _headers, {"Dept","Count"}, IF(COUNTA(I4:I6) < 3, _headers, LET( pre_filter, FILTER(Table1[[Name]:[Dept]], (Table1[Date]>=I4) * (Table1[Date]<=I5) * (Table1[Status]=I6), NA()), col_dept, CHOOSECOLS(pre_filter, 2), _depts, SORT(UNIQUE(Table1[Dept])), CntRws, LAMBDA(dept, ROWS( UNIQUE( FILTER(pre_filter, col_dept = dept, NA()) ) ) ), VSTACK( _headers, HSTACK(_depts, IFNA(BYROW(_depts, CntRws), 0)) ) ) ) )- Thank you for this. It is working perfectly.
=LET(filtered,
UNIQUE(FILTER(HSTACK(C2:C23,B2:B23),(A2:A23>=H3)*(A2:A23<=H4)*(E2:E23=H5))),
count,
DROP(REDUCE("",SEQUENCE(ROWS(filtered)),LAMBDA(u,v,VSTACK(u,HSTACK(INDEX(filtered,v,),SUM(N((INDEX(filtered,,1)=INDEX(filtered,v,1)))))))),1),
IFERROR(UNIQUE(CHOOSECOLS(count,1,3)),"no matching data"))
With Office 365 or Excel for the web this formula returns the expected result in my sample file.
- Thanks a ton!! This is working perfectly fine !
