Counting unique numbers with criteria

Hi All -

I'm struggling with creating a formula. The data has the following columns:
Membership number, Member name, Active(Yes/No), Group, consultant1, consultant2, consultant3

Note, there can be multiple member names for each membership number.

I need a formula that will count the total number of unique memberships for a consultant, when that consultant is listed in either consultant1, consultant2, consultant3, and when that membership is listed as active.

=SUM(--(FREQUENCY(IF((consultant1=E2)*(Active="Yes"),Membership number),Membership number)>0))+(--(FREQUENCY(IF((consultant2=E2)*(Active="Yes"),Membership number),Membership number)>0))+(--(FREQUENCY(IF((consultant3=E2)*(Active="Yes"),Membership number),Membership number)>0))

where 'E2' is a cell that references the consultant. But it isn't delivering accurate results when I check the data manually. Is there a better/shorter way?

TIA!

A pivot table might be able to do this painlessly.

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"