I have an Excel spreadsheet that consists of four tabs--Metrics, Calendar year 2012_activities, Company Names, and Contacts.
On the Metrics Tab I am trying to use COUNTIFS to count the unique number of Activity IDs (ActivityID) that fall under a specific meeting activity type (ACTIVITY_TYP_NM) for a specific office region (OFFICE) and filtered by the company name (COMPANY_NM) entered on the metrics tab in cell b3. I am using named range for the company name (COMPANYNMRNG) to make the formula less complicated.
As you you can see in the attached file, I cannot get this to count the distinct Activity ID--it counts duplicates as well. I have tried using pivot tables with dynamic ranges, but that does not work well either. Is there a way to accomplish this in Excel?