Friday, July 27, 2012

yogi_Keep A Running Count Of Monitored Calls For A Specified Agent By Month

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #658   Jul 27, 2012     www.energyefficientbuild.com.


user AnswerFirst said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/dkRx3tKpfww)
How to calculate the number of items in one column dependent on values in an another column (similar to =arrayformula)
This is way too hard to explain without just sharing the sheet:
https://docs.google.com/spreadsheet/ccc?key=0AliYViHYAwaNdGRBb1JxOHo5REJlMHl6OU5Ld3VId2cI need to auto-populate column B with the cumulative number of calls that an agent (column F) has made in the month (column A) ... I found this formula that seems to work for any given row (i.e. this would work for row 2): 
=arrayformula(sum((A:A=6)*(F:F="ACM - Alaina McGinnis"))) 
but I can't figure out how to make this work for all rows without manually rewriting the formula in each row.  
It doesn't matter if this is solved with a formula or a script but I need the value to be calculated each time the form is submitted.  Thoughts, suggestions?  Thank you!

----
Thanks yogia,
To answer your questions:
1. The data is realistic; this is the exact sheet and form we use in production.
2 & 3. The calculation that I'm struggling with is on Sheet1: in column B I would like to calculate the total number of monitored calls that an agent (column F) has had for any given month (column A)  So, if Jane Doe (column F) was monitored on 3 calls in the month of July (represented by a 7 in column A) then the value in column B would be 3.  I've manually filled in the values for B2:B10 to show you what I would expect to see there.  Cell B11 has been left blank
4. I'm not sure a separate summary table would work for this as I need the value in column B to populated automatically as rows are added to this sheet.  This spreadsheet is populated based on the results of a form (which is visible to the public in case you want to glance at it.)
Thank you for your help and time!  :)

---------------------------------------------------------------------------------------------
following is a solution to the problem

No comments:

Post a Comment