Google Spreadsheet Post #1459
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Dec-31, 2013
question by Meredith Poynter (http://productforums.google.com/forum/?zx=76qbjtbjhn60#!category-topic/docs/spreadsheets/6QdasYI2-SE)
How to make this SUMPRODUCT (or COUNTIFS) formula to work in Google Spreadsheets?
The original table I pasted was a very basic and generic form of what I'm working on. This is the real thing (with identifying info changed or deleted)
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Dec-31, 2013
question by Meredith Poynter (http://productforums.google.com/forum/?zx=76qbjtbjhn60#!category-topic/docs/spreadsheets/6QdasYI2-SE)
How to make this SUMPRODUCT (or COUNTIFS) formula to work in Google Spreadsheets?
The original table I pasted was a very basic and generic form of what I'm working on. This is the real thing (with identifying info changed or deleted)
https://docs.google.com/ spreadsheet/ccc?key= 0ApOYbVKFbSMNdFVGN0JIdmhIRVRPQ TZfQ043WndtS3c&usp=sharing
The data table is the "Form Responses 2" tab as the info feeds in from a form.
I'm looking for a formula to put in the cells 'Advocacy Engagement'!P3:P32. The results of the formula should display the number of advocates that have an engagement level of "3" ('Form Responses 2'!I2:I105) for the legislator that same row. This must be done without counting duplicate entries of the same Advocate by Legislator ('Form Responses 2'!D2:G105). In other words, these advocates could be entered multiple times ex. Bob, CEO of K Bank may be entered on the form 2 or more times. This might be because he is advocating with more than one legislator, or he is entered in mulptiple times because his engagement level went up with a particular legislator, or simply multiple notes associated to him has been entered. I only want him to be counted once for that legislator in that row and only if he has reached a level 3 engagement level. The result should be a count of the number of advocates that meet this criteria for that legislator.
As an aide to the formulas on the "Advocacy Engagement" tab, I used column A to show the full name of the Legislator so it matches the legislator name in 'Form Responses 2'!D2:D105.
Also, know that this can not be a count of the "3"s in 'Advocacy Engagement'!J3:O32 as those formulas show the highest level of engagement for that Advocacy Type (column header) regardless how many advocates there are.
I don't know of any other links to the same or similar question like mine. I searched before I posted my question.
Hopefully this answers your questions and you can help me with this. Please let me know if you have any other questions.
Thanks in advance!
-------------------------------------------------------------------------------------------------------
No comments:
Post a Comment