CODEWITHSUNDEEP
excelexcel-formulacountifdistinct-values
Chris
Chris

Reputation: 249

Excel: Count distinct numerical values if string condition matches

I have a list of employee names on one tab and another tab with orders shipped by employees and the month they were shipped going back 12 months. I'd like to calculate the average number of products shipped per employee per month, but I need to know how many months they were here to do that. So what I'd like to do is essentially write a formula that says give me the count of the distinct number of months they've been shipping products.

Sample employee data:

Tab with employee names

And here's the sample data on the individual shipments:

Individual shipment data

So in short, I need to know that Joe Smith shipped those 250 products across 3 distinct months to see he averages 83.3 shipments per month. Again, because there are many new people who have come onboard in the last 12 months, I can't just divide them all by 12 and need to know how many months they were shipping items in.

Upvotes: 2

Views: 720

Answers (4)

VBasic2008
VBasic2008

Reputation: 54807

Average by Count of Uniques

=LET(Shippers,B2:B11,Months,C2:C11,uShippers,E2:E4,uProducts,F2:F4,
    uMonths,BYROW(uShippers,LAMBDA(uShipper,
        ROWS(UNIQUE(FILTER(Months,Shippers=uShipper))))),
IFERROR(uProducts/uMonths,""))

enter image description here

Upvotes: 2

David Leal
David Leal

Reputation: 6759

You can use this array version, which spills all the results at once:

=LET(empl, A2:A4, prods, B2:B4, shipper, B7:B16, months, C7:C16,
  ux, MAP(empl, LAMBDA(e, COUNT(UNIQUE(FILTER(months, shipper=e))))), prods/ux)

Here is the output: excel output

It is also possible not using MAP but it is a verbose solution:

=LET(empl, A2:A4, prods, B2:B4, shipper, B7:B16, months, C7:C16,
  left, TRANSPOSE(N(shipper=TOROW(empl))), right, N(months=TOROW(UNIQUE(months))),
  cnts, N(MMULT(left, right)>0), ux, MMULT(cnts, SEQUENCE(ROWS(cnts),,1,0)), prods/ux)

Replacing TOROW with TRANSPOSE it should work for older Excel versions.

Upvotes: 1

user11222393
user11222393

Reputation: 5471

FILTER Shipper and Month based on Shipper column with criteria Employee name. Apply UNIQUE on filtered array to get only unique values (name + month number). Use COUNT to get active months. Divide Products Shipped by it.

Result:

enter image description here

Upvotes: 2

VinodA
VinodA

Reputation: 111

This is how I would have done it, create a Pivot table(Insert->Pivot Table) with Months as column, Employee as row and the values as count of shipments. Once you have the Pivot table, your life becomes easier. Now you do a count of the employee row (COUNT(COL-1:COL-X)) to count the total months a particular employee showed up.You now have count of shipments and a count of months. You can calculate the average. Not sure I can think of anything else easier.

Upvotes: 0

Related Questions