Frequency or CountIF

[javijuan]

Hello, I've been searching this for weeks with no luck. I need to calculate daily productivity. I monitor calls in a call center. On average I have to monitor 12 - 15 calls a day. I keep track of which person I monitor and when I monitored them. I have to monitor 4 calls per person per month. So I divide my excel sheet by Round 1, Round 2.. for call 1 thru 4. Sometimes I have to work on round one and two the same day. so they are in different columns. I was calculating the average by counting how many calls done in a day, then divide it by how many dates on the column. This works, but it does not take in consideration if I work other rounds on a particular day. On my example sheet attached, column E (round 1), J (Round 2), O (round 3). On E I have 2 dates, J 1 date.. but as you can see I have done calls on 6/1/15 in both round 1 and 2. So the productivity for 6/1/15 should be 10 calls, 6/3/15 4 calls, and then 1 on 5-7. The average daily productivity should be: 17/5 = 3.4 calls per day.

sorry if it doesn't make sense.. It's hard for me to explain.. I appreciate any help.

PS: There will be lots of numbers and letters in between round columns.. F-I.. call ID, scores.. so when counting cells with information I can't do a range from E-O for example.. I have to select each column. Thanks.example.xlsx

[FlameRetired]

Examining the uploaded Excel file I don't understand the logic of 17 and 5 for 17/5. I could understand if it were 18 and 6.

So the productivity for 6/1/15 should be 10 calls, 6/3/15 4 calls, and then 1 on 5-7.

I can follow the blue part but then how do you get the remaining call counts in "and then 1 on 5-7."?

What about the call made on the 4th?

Could you clarify further?

[javijuan]

Sorry.. Yes, the 4th will be one too.

But, I can count the frequency with this form: =SUM(IF(FREQUENCY(E2:E33,E2:E33)>0,1)). then see the frequency on round 1-4 then divided by 4 and get the average.
The problem is that when I use that formula to count the other columns.. it will count the same date twice if I work on round 1 and 2 the same day. therefore the formula wont be accurate.

Thanks

[kvsrinivasamurthy]

Try this

Please Login or Register  to view this content.

(formula in T3 in file)

[javijuan]

It works.. but if I enter any numbers in colums F-I, K-N.. it also count that.. i need the formula to just check columns E,J and O.

Thanks for the reply.

[kvsrinivasamurthy]

Try this ARRAY formula,

Please Login or Register  to view this content.

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.

[javijuan]

WOW, I wouldn't figure that out in a million years.. Thanks...

[kvsrinivasamurthy]

Welcome.
Thanks for the compliments.