Excel – Count only instances of unique values in two columns

Please see image below, I am looking to populate cells G8-G12 with a value of how many times the day appears in Column B and has a unique date in Column A. So as an example, Wednesday would count 2 as it appears on 28/02/2024 and 06/03/2024.

How do I do this?

I’ve tried SUMPRODUCT and COUNTIFS but not managed to get what I wanted, for example I used

(=SUMPRODUCT((1/COUNTIFS(A2:A12,A2:A12,B2:B12,F8))) 

in cell G8 but it’s incorrect.

>Solution :

Here is what Older versions of Excel need to use:

=SUMPRODUCT((F8=B$2:B$12)/(COUNTIFS(B$2:B$12,B$2:B$12,A$2:A$12,A$2:A$12)))

Or, Using Newer Versions of Excel:

=SUM(--(UNIQUE(FILTER(A$2:A$12,F8=B$2:B$12))<>""))

Also using Pivot Table to get unique distinct count one needs to add the data in the Data Model to apply the summarization with Distinct Counts — the feature is available in Windows Excel 2013+ and Excel 365 (Windows)

To use follow the steps:

• First convert the source ranges into a table and name it accordingly, for this example I have named it as Table_1

• Select some cell in your data and click on Insert Tab -> Click on Pivot Table –> The Table/Range will shows as Table_1, Click on New Worksheet or Existing Worksheet as per your choice, –> If latter select the cell location and click on Add this data the Data Model.

• On right Pivot Table Fields Pane appears, place the Day in Rows Area and Date in Values area,

• Click on the values in the pivot table, right click –> Summarize Values By –> Distinct Count.

• Note that in the pivot table i have used a custom sorting order, which one can apply by using a custom lists in the file options menu.