How Do I Count Unique Values in Excel

June 30, 2023

The data below is a small extract of my dataset. I have been trying to work with a formula to get a count of the unique number of times a day of the week appears for a room in given date range.

Here is the formula I tried to use:

=FILTER(Table1,Table1[Exam Rm Number]=I2)*(Table1[Day of Wk]=I1)

Here is the result I am getting:

Can anyone help me with a formula to return what I am looking to do?

>Solution :

If I have understood correctly, then you would need to use a formula like as below:

• Formula used in cell B12

=LET(
     a,Table1[ApptDate],
     b,Table1[Day of Wk],
     c,Table1[Exam Rm Number],
     d,HSTACK(a,b,c),
     ROWS(UNIQUE(FILTER(d,(b="Thursday")*(c="Room 1")))))