count totally unique values, that occur in a certain year

trying to create a simple tracking document for new client referrals, working in Office 365 I used =COUNTA(UNIQUE(B2:B6)) to count the number of unique names across all years, which tells me the ...

excel

office 365

PeterBartholomew1

Silver Contributor

Jan 30, 2022

atables1

I am not sure that I have completely understood the requirement but, for me, the key formula would seem to be

= MINIFS(Date, Name, UNIQUE(Name))

giving the first referral date for each name. That can then be dressed up to provide annual new referral numbers or any specific monthly referral number

= LET(
    distinctYears, UNIQUE(YEAR(Date)),
    newReferral,   UNIQUE(Name),
    referralDate,  MINIFS(Date, Name, newReferral),
    referalYear,   YEAR(referralDate),
    MAP(distinctYears, LAMBDA(yr, SUM(N(referalYear=yr))))
  )

and

= LET(
    distinctYears, UNIQUE(YEAR(Date)),
    newReferral,   UNIQUE(Name),
    referralDate,  MINIFS(Date, Name, newReferral),
    referralYear,  YEAR(referralDate),
    referralMonth, MONTH(referralDate),
    SUM((referralMonth=selectedMonth)*(referralYear=selectedYear))
  )

UniqueYearMonth.xlsx13 KB

atables1
Copper Contributor
Jan 30, 2022
PeterBartholomew1 thank you so much! i do not see these formulas available in office 365 😞

Forum Discussion

count totally unique values, that occur in a certain year

Resources