Search code examples
excelfilteringmultiple-conditionsunique-values

Count unique values, with condition in other column

I have a dataset with orders from multiple days. I would like to map this in a matrix (100 cells by 100 cells) to see where orders appear.

I allready added a column to the dataset which determines in which region of the 100x100 matrix the order belongs. I already mapped the data in the matrix, but now i only want the total days that at least 1 order occurs in that specific region.

So

My dataset looks like this:

x-coordinate | y-coordinate | Depot (y/n) | Date
45,423       | 13,756       | 1           | 3-4-2020 
47,263       | 12,351       | 1           | 5-4-2020

etc.

Current formula in the matrix:

=COUNTIFS($B$2:$B$11506;"<"&$P7;$B$2:$B$11506;">"&$P8;$C$2:$C$11506;">"&Q$6;$C$2:$C$11506;"<"&R$6;$D$2:$D$11506;"<>"&0)

This checks if the value is within the range, and excludes "0", (This is the depot, not an order)

sumproduct(1/countif(array:array)) returns the unique values, however this does not return the unique values that obey the geographical conditions.

How can I count the unique days on which at least 1 order occurs for a given region?

Thanks in advance

Solution

  • I think I found a way, but there might be cleaner sollutions...:

    In column E, I merged the x-coordinate, y-coordinate, and date. By:

    =A2&"-"&B2&"-"D2"

    In the first row, I wrote a 1, as this is always unique/first occurence of this combination. For the rest i dragged down the following.

    =IF(COUNTIF($E$2:$E35;J36);0;1)

    It checks if this combination has already occured above, if yes, then 0 else 1.