Forum Discussion

and_rogynous's avatar
and_rogynous
Copper Contributor
Nov 29, 2022

Counting Unique Values with Multiple Criteria

I am using the following formula to count the amount of unique freight carriers I use each day at work:

=SUM(--LEN(UNIQUE(FILTER(Orders[Carrier],Orders[Shipped]=Dates[@[11/22]],"")))>0))

Orders[Carrier] has the name of the freight carrier for each order, Orders[Shipped] has the date the order shipped, and Dates[@[11/22]] is from a table that is just a list of dates to reference in formulas so that certain ones are easier to copy/paste.

But now, I'm trying to modify the same formula to count the amount of unique carriers used each month:

=SUM(--LEN(UNIQUE(FILTER(Orders[Carrier],AND(Orders[Shipped]>=AT3,Orders[Shipped]<=AT32),"")))>0))

AT3 shows 11/1/2022, and AT32 shows 11/30/2022.

But...this formula returns 0. How do I make the formula so that it counts the amount of unique carriers used between 11/1 and 11/30?

excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Nov 29, 2022

    and_rogynous 

    Since you are using FILTER, you may have LAMBDA.  AND can be made to operate pairwise by placing it with the MAP helper function

    = UNIQUE(
     FILTER(Orders[Carrier],
        MAP(Orders[Shipped],
           LAMBDA(shippingDate,
              AND(shippingDate>=start, shippingDate<=end)
           )
        )
     )
  )

    Naming the Lambda functions can also be useful

    = UNIQUE(
     FILTER(Orders[Carrier],
        MAP(Orders[Shipped], Betweenλ(start, end))
     )
  )

    where

    Betweenλ 
= LAMBDA(start,end,
     LAMBDA(shippingDate,
        AND(shippingDate>=start,shippingDate<=end
     )
  )
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 29, 2022

    and_rogynous 

     

    AND returns only a single TRUE/FALSE value, not an array. Try

     

    =SUM(--LEN(UNIQUE(FILTER(Orders[Carrier],(Orders[Shipped]>=AT3)*(Orders[Shipped]<=AT32),"")))>0))

Resources