Forum Discussion
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 ...
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
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
The functions I used should be available in Excel in Microsoft 365...
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?
- 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 ❤️