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/
=arrayformula(sum((A:A=6)*(F:
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
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/
=arrayformula(sum((A:A=6)*(F:
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