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

excel

Lorenzo

Silver Contributor

Jun 05, 2024

ParthaPratimGhosh

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))
      )
    )
  )
)

sample_ParthaPratimGhosh.xlsx12 KB

ParthaPratimGhosh

Copper Contributor

Jun 05, 2024

Thank you for this. It is working perfectly.

Forum Discussion

Counting unique records with multiple condition in excel