Excel - Count only instances of unique values in two columns

Question

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.

Answer 1

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.

---

Answer 2

You could simply do a pivot-table with Day as row and Date as value then count quantity