Search code examples
excelexcel-formulauniquemultiple-columns

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.

Screenshot

Solution

  • Here is what Older versions of Excel need to use:

    enter image description here

    =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:

    enter image description here

    =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)

    enter image description here

    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.

    enter image description here

    • 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.