Forum Discussion

atables1's avatar
atables1
Copper Contributor
Jan 29, 2022

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 total number of new client referrals across all years

 

But I need to figure out a way to count new client referrals in a certain year (ideally selected from a drop down menu in G5 that I haven't created yet) ... it's very important that names that are counted in a certain year are unique ACROSS ALL YEARS

 

 

e.g. if you select for certain year, let's say 2022, to see count of new referrals .... John Smith is NOT a new referral despite his name showing up once uniquely in 2022 and should not be counted since he was already a client in 2021

 

e.g. if you select for year 2022, there is 1 unique new referral (Music, Maker)

 

e.g. if you select for year 2021, there is 3 unique new referrals (all 3 names, since no referrals occurred before 2021)

 

I also need a way to count new client referrals in a certain year AND a certain month from a future drop down in G6 if you can include that in a formula

 

e.g. if you select for year 2022 and month February, there is 0 unique new referrals (no referrals occurred in Feb 2022)

 

I've been trying with COUNTIF, UNIQUE, SUM, FREQUENCY, MATCH, YEAR, MONTH, IF for hours!! thanks

excel
office 365

8 Replies

  • PeterBartholomew1's avatar
    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))
  )
    • atables1's avatar
      atables1
      Copper Contributor
      Jan 30, 2022
      hi THANK YOU SO MUCH

      I created the helper columns but Office 365 does not appear to have the array functions you use 😞 they only have an "array to text" option

Resources