Tuesday, December 31, 2013

yogi_MultiConditional Count Of Unique Advocates With Engagement Level Of 3 By Legislator

                                          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)

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!