Excel Count distinct numerical values if string condition matches

0 votes

On one tab, I have a list of the names of my employees. On another tab, I have orders that my employees have shipped, together with the month they shipped them, going back a full year. To get the average quantity of products supplied per employee each month, I must first know how long they have worked for the company. Therefore, I want to create a formula that basically says, "Give me the count of the specific amount of months they've been shipping things."

Sample employee data:

Tab with employee names

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

Individual shipment data

So in order to determine that Joe Smith ships an average of 83.3 items per month, I need to know that he sent those 250 products over the course of three separate months. Again, I can't just divide them all by 12 and need to know how many months they were shipping products because there were so many new hires in the past 12 months.

Feb 10, 2023 in Others by Kithuzzz
• 38,010 points
339 views

1 answer to this question.

0 votes

FILTER Shipper and Month based on Employee name in the Shipper column. To obtain just unique data (name + month number), apply UNIQUE to the filtered array. To calculate active months, use COUNT. Distribute the Products Sent by it.

Result:

enter image description here

answered Feb 10, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

(Excel) If cell is greater than <condition> then minus <number>

The IF function to calculate B5 (amount ...READ MORE

answered Nov 7, 2022 in Others by narikkadan
• 63,700 points
1,007 views
0 votes
1 answer

Determine if calculation between 2 date time values is < 72 Hours in excel

Actually, Excel is quite accommodating in this ...READ MORE

answered Nov 12, 2022 in Others by narikkadan
• 63,700 points
680 views
0 votes
1 answer

Count cells in excel with populated values

Use: =SUMPRODUCT(--(CHOOSE({1,2,3,4,5},A1, A4, A6, A8, A10)<> ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,700 points
365 views
0 votes
1 answer

How to fix a circular reference error by if condition in excel file?

Circular reference in this context refers to ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 63,700 points
516 views
0 votes
1 answer

Excel COUNTIF "Column D" year equals 2015 and Column L equals "15 or greater"

COUNTIFS() with an S allows the ability of multiple ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
753 views
0 votes
1 answer

Excel Formula Query on Summing Annual Leave Days and Half Days

Try Something like =SUM(COUNTIF($A$1:$A$8,"A"),COUNTIF($A$1:$A$8,"HA")/2) - total count ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
1,556 views
0 votes
1 answer

How to simplify adding multiple countifs formula in excel

If the Excel version you are using ...READ MORE

answered Nov 26, 2022 in Others by narikkadan
• 63,700 points
471 views
0 votes
1 answer

Newbie needs Excel suggestions with a simple script

Formula to Count the Number of Occurrences ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,700 points
386 views
0 votes
1 answer

Counting distinct values in excel - frequency function

You can use COUNTIF to count the ...READ MORE

answered Oct 20, 2022 in Others by narikkadan
• 63,700 points
336 views
0 votes
1 answer

Excel Formula - if values in columns all contain X then return

You can use the AND function: = IF(AND(A3="OK";B3="OK";C3="OK");"everything ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 63,700 points
1,262 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP