Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

How to count number unique names in one column where there is a specific date in 2nd column

Copper Contributor

I have Column A with many servernames which are often duplicated.
Column B has a variety of dates.

I want to count the number of unique servers for a specified date.

e.g. I have this:

server11-Feb
server11-Feb
server11-Feb
server21-Feb
server32-Feb
server32-Feb
server42-Feb
server42-Feb
server42-Feb
server42-Feb
server52-Feb
server52-Feb

 

I want to count the unique number of servers for 1-Feb and the unique number of servers for 2-Feb.
The date cells are formatted as dates so although the cell shows 1-Feb, the cell data is actually 1/02/2024. 
Please can someone help advise the combo of functions which will do this?

 

4 Replies

@JennyMarfell-Jones You may try the following formula-

=HSTACK(UNIQUE(B1:B12),MAP(UNIQUE(B1:B12),LAMBDA(x,ROWS(UNIQUE(FILTER(A1:A12,B1:B12=x))))))

Harun24HR_0-1706069025005.png

 

 

best response confirmed by JennyMarfell-Jones (Copper Contributor)
Solution

@JennyMarfell-Jones To return a distinct count of server names for a specified date, the SUM / COUNTIFS method can be used with the UNIQUE function (Excel 2021 or later). For example, if the input cell for the specified date is cell D2, the following formula can be used:

 

=SUM(IF(COUNTIFS(B2:B13, D2, A2:A13, UNIQUE(A2:A13)), 1))

 

COUNTIFS Distinct CountCOUNTIFS Distinct Count

 

With Excel for MS365 (or Excel for the Web), you can also create a summary report for all dates in the dataset using the HSTACK and BYROW functions:

 

=LET(arr, UNIQUE(B2:B13),
HSTACK(arr, BYROW(arr, LAMBDA(r,
   SUM(IF(COUNTIFS(B2:B13, r, A2:A13, UNIQUE(A2:A13)), 1))))))

 

Alternatively, the ROWS / FILTER method can be used for a specified date:

 

=LET(data, UNIQUE(A2:B13), incl, DROP(data,, 1)=D2,
IF(OR(incl), ROWS(FILTER(data, incl)), 0))

 

Or, to create a summary report for all dates in the dataset:

 

=LET(a, UNIQUE(A2:B13), b, DROP(a,, 1), c, UNIQUE(b),
HSTACK(c, BYROW(c, LAMBDA(r, ROWS(FILTER(a, b=r))))))

 

Please see the attached workbook, which contains all of the examples shown above...

@Harun24HR 

Hi Harun, that's awesome, thanks for responding and helping out!  Will try that out!

Cheers

Jenny

Hi @djclements
Thanks so much for taking the time to help! Have just tried that =SUM command and it worked a treat!!
Cheers
Jenny
1 best response

Accepted Solutions
best response confirmed by JennyMarfell-Jones (Copper Contributor)
Solution

@JennyMarfell-Jones To return a distinct count of server names for a specified date, the SUM / COUNTIFS method can be used with the UNIQUE function (Excel 2021 or later). For example, if the input cell for the specified date is cell D2, the following formula can be used:

 

=SUM(IF(COUNTIFS(B2:B13, D2, A2:A13, UNIQUE(A2:A13)), 1))

 

COUNTIFS Distinct CountCOUNTIFS Distinct Count

 

With Excel for MS365 (or Excel for the Web), you can also create a summary report for all dates in the dataset using the HSTACK and BYROW functions:

 

=LET(arr, UNIQUE(B2:B13),
HSTACK(arr, BYROW(arr, LAMBDA(r,
   SUM(IF(COUNTIFS(B2:B13, r, A2:A13, UNIQUE(A2:A13)), 1))))))

 

Alternatively, the ROWS / FILTER method can be used for a specified date:

 

=LET(data, UNIQUE(A2:B13), incl, DROP(data,, 1)=D2,
IF(OR(incl), ROWS(FILTER(data, incl)), 0))

 

Or, to create a summary report for all dates in the dataset:

 

=LET(a, UNIQUE(A2:B13), b, DROP(a,, 1), c, UNIQUE(b),
HSTACK(c, BYROW(c, LAMBDA(r, ROWS(FILTER(a, b=r))))))

 

Please see the attached workbook, which contains all of the examples shown above...

View solution in original post