I have a large spreadsheet with appointments containing multiple clients. Spreadsheet contains a date column and a client number column. The client number column often duplicates in multiple rows to show criteria in other columns that is needed. I cannot remove the duplicates. What formula can I use to count the number of appointments for the date for only distinct client numbers?

I am able to use a countifs formula to get the total, however it is counting the client numbers each time. (for the below example I get 6, where the correct count I am looking to get is 4, as I only want to count each client number for the date once)

I have a large spreadsheet with appointments containing multiple clients. Spreadsheet contains a date column and a client number column. The client number column often duplicates in multiple rows to show criteria in other columns that is needed. I cannot remove the duplicates. What formula can I use to count the number of appointments for the date for only distinct client numbers?

I am able to use a countifs formula to get the total, however it is counting the client numbers each time. (for the below example I get 6, where the correct count I am looking to get is 4, as I only want to count each client number for the date once)

• excel
• excel-formula

• 1 a look at the unique() function. – Solar Mike Commented Mar 14 at 18:16
• =GROUPBY(HSTACK(INT(B2:B7),A2:A7),A2:A7,COUNT) – P.b Commented Mar 14 at 19:18
• =GROUPBY(INT(C2:C7),A2:A7,LAMBDA(x,ROWS(UNIQUE(x))),,0) – Mayukh Bhattacharya Commented Mar 14 at 19:20
• 1 @P.b Sir, I think OP is looking for this per client say: =GROUPBY(HSTACK(INT(C2:C7),A2:A7),A2:A7,LAMBDA(x,ROWS(UNIQUE(x))),,0) – Mayukh Bhattacharya Commented Mar 14 at 19:22
• 1 @MayukhBhattacharya I think you're correct – P.b Commented Mar 14 at 20:04

1 Answer 1

Counting unique clients per date/time:
B9: =UNIQUE(Table1[Appt Details])
A9: =COUNT(UNIQUE(FILTER(Table1[Client Number],Table1[Appt Details]=C10)))

Counting unique clients per date:
B13: =UNIQUE(INT(Table1[Appt Details]))
A13: =COUNT(UNIQUE(FILTER(Table1[Client Number],INT(Table1[Appt Details])=C14)))

Assuming that Table1 is A1:B7, with header in first column.