count totally unique values, that occur in a certain year

[atables1]

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

[#2]

@atables1 

See the attached sample workbook. I used two helper columns (and I used the month number instead of the month name).

[#3]

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

[#4]

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

[#5]

@atables1 

The functions I used should be available in Excel in Microsoft 365...

[#6]

@Hans Vogelaar they are not, I'm confused

[#7]

@Peter Bartholomew thank you so much! i do not see these formulas available in office 365 :(

[#8]

@atables1 

I have no idea what you mean.  In the workbook that I attached the formula in D2 is

=(COUNTIFS($A$2:$A$43,"<"&DATE($H$5,1,1),$B$2:$B$43,B2)=0)*(YEAR(A2)=$H$5)

In E2:

=(COUNTIFS($A$2:$A$43,"<"&DATE($H$5,$H$6,1),$B$2:$B$43,B2)=0)*((YEAR(A2)=$H$5)*(MONTH(A2)=$H$6))

In I5:

=SUM(--(UNIQUE(IF(D2:D43,B2:B43,""))<>""))

The rest are the same, only with different cell references. Nothing with ARRAY in the name.

Are you sure that you have Microsoft 365?

[#9]

oh no! I figured it out. When i downloaded your sample workbook it opened in Google Sheets on my gmail instead of my OneDrive (weird), so I was looking at the formulas in Google Sheets form which changes the formulas to use a few of their ARRAY functions

It worked and I am SO RELIEVED THANK YOU <3