Forum Discussion
JennyMarfell-Jones
Jan 24, 2024Copper Contributor
How to count number unique names in one column where there is a specific date in 2nd column
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: serv...
- Jan 24, 2024
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 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...
djclements
Jan 24, 2024Bronze Contributor
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 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...
JennyMarfell-Jones
Jan 29, 2024Copper Contributor
Hi djclements
Thanks so much for taking the time to help! Have just tried that =SUM command and it worked a treat!!
Cheers
Jenny
Thanks so much for taking the time to help! Have just tried that =SUM command and it worked a treat!!
Cheers
Jenny