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)

https://docs.google.com/spreadsheet/ccc?key=0ApOYbVKFbSMNdFVGN0JIdmhIRVRPQTZfQ043WndtS3c&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!

-------------------------------------------------------------------------------------------------------