2

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.

Improve this question
1
  • 1
    FILTER(), UNIQUE() and COUNT() could be a good combo. Commented Feb 9, 2023 at 18:21

4 Answers 4

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This for sure solves this issue in the example I gave, thank you! Though I'm now finding a little bit of a more complicated struggle with this because I simplified the shipper name. In my real data set, I have some other data I can't necessarily separate. For instance, some have multiple shippers involved, so the Shipper column may be "aa, bb, Team A" or "aa, cc, Team C." What you gave me here works on this but any idea why adding wildcards before and after the F2 in your example won't work, like ""&F2&""? I didn't think the wildcard aspect would be the next hangup I'd come across.
Unfortunately,FILTER doesn't work with wildcards.Even if it would, formula would break: it would still return "full" cell for example "aa, bb, Team A" and "aa, cc, Team C" without extracting any text. So both with aa would still be 2 unique values. You could try something like this: =SUMPRODUCT(--(COUNTIFS(B:B,""&F2&"",C:C,SEQUENCE(12))>0)) .Wildcard works with COUNTIF so it will count if B includes F2 and if C is equal to one of array from 1 to 12 (SEQUENCE(12) ofr 12 months). >0 will give TRUE for those months that have count>0, and sumproduct -- will convert TRUE to 1's and sum it up
Using asterisk made it italic instead of writing symbol instead. There should be asterisk symbols between ""&F2&"" quotes in formula =SUMPRODUCT(--(COUNTIFS(B:B,""&F2&"",C:C,SEQUENCE(12))>0))
2

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

Improve this answer

Comments

1

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.