• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

Countifs to count distinct values

Greetings,

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?

Thanks!

-wasmithpfs
COPY-2012-metrics-IA-ACTIVITIES-.xlsx
0
wasmithpfs
Asked:
wasmithpfs
  • 4
  • 3
1 Solution
 
aikimarkCommented:
@wasmithpfs

You have a circular reference at T4.  Your JERSEYRNG should be pointing to T3 instead of T4.
0
 
wasmithpfsAuthor Commented:
Thanks for highlighting that error. Still, with the correction, the count does not work. Check company A& L Goodbody. Ther is  one meeting in LOndon, bit the count  shows as zero.

-Wasmithpfs
0
 
aikimarkCommented:
the COUNTIFS() function uses criteria ranges for the parameters after the first one.  As written, your formulas will not work.

Criteria ranges must have a column header row (one or more cells)
One or more rows may immediate follow the column header row with the criteria.

I explain criteria in the webinar I presented in December:
http://www.youtube.com/watch?v=0M4gePK8510

You may need to use the DISTINCTCOUNT() function.

============
There are only two rows of data in your METRIC worksheet.  Perhaps it would be more illustrative to use the filtered rows from the Calendar year 2012_activities worksheet.  What should the Activity_ID counts be for the filtered (Hong Kong office) rows for ACTIVITY_TYP_NM
MTG - Function
MTG - Meeting
MTG - Presentation
MTG - Breakfast/Lunch/Dinner
MTG - Function
?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
wasmithpfsAuthor Commented:
Hi aikimark,
Thanks for responding so quickly.

First, on the metric sheet, I am using a pivot table that is filtering on the company, that is why there are only two rows of data. This can vary depending on the company chosen.

Secondly, when a company is chosen, I need a distinct count of activity ids that fall under
MTG- Meeting,
MTG- Breakfast/Lunch/Dinner
MTG-Presentation
INT-Walkers  Win
---for the regional office:
BVI
CAYMAN
DUBLIN
DELAWARE
DUBAI
HONG KONG
JERSEY
SINGAPORE

So for instance, there were two distinct MTG-Meetings for company AKIN GUMP in Hong Kong (Activity ID 265970, 245985).

This is why I am using the COUNTIFS function because I am trying base my count on multiple criteria and I need to NOT count duplicate Activity ID's. I was unable to find a DISTINCTCOUNT() function In Excel 2010.

Is there way to do this with a pivot table or can a formula be written using the appropriate function?  Please advise.

wasmithpfs :)
0
 
aikimarkCommented:
There seems to be at least two ways to do this.

If you're using Excel2010, you can use the powerpivot wizard:
http://www.pivot-table.com/2012/02/22/unique-count-in-excel-pivot-table-with-powerpivot/

In earlier versions of Excel, you should be able to create a new column that would be equal to 1 or 0, depending on whether it was the first item in a group.  You include this new field in your pivot table.
http://www.contextures.com/xlPivot07.html#Unique

Notes:
* You might need to pre-sort your data in order for the sumproducts() formula to work.
* I'm not sure if the addressing shown in the example is correct.  The example uses absolute references for both column and row and I'm not sure if that is correct.
0
 
aikimarkCommented:
I used the AND() function in the attached Excel2003 file.  I had to sort by the Account_ID column first and then sort by the same order as the pivot table would be displaying the results.
COPY-2012-metrics-IA-ACTIVITIES.xls
0
 
wasmithpfsAuthor Commented:
Hi aikimark,

Thanks for your feedback. I used your suggestion of adding  column  (IDCount) and used a countifs statement to assign a 0 or 1 to the if there was a duplicate:

IF(COUNTIFS(A$1:A13,A13,G$1:G13,G13,V$1:V13,V13)=1,1,0)

where column A is the activity id, column G is the company name and column V is the office location

Then on the Metrics sheet I created a table where in column A I listed the offices
BVI
CAYMAN
DUBLIN
DELAWARE
DUBAI
HONG KONG
JERSEY
SINGAPORE

and in the first row I listed the Meeting Types labels:
MTG- Meeting,
MTG- Breakfast/Lunch/Dinner
MTG-Presentation
INT-Walkers  Win

Then at each intersection of Office and Meeting type I used a  SUMIFS function to count the
activity Ids, by meeting type and office location,for each meeting type type,  filtered by company name entered  in cell B3 (a named range COMPANYNMRNG):

SUMIFS('Calendar year 2012_activities'!W:W,'Calendar year 2012_activities'!G:G,COMPANYNMRNG,'Calendar year 2012_activities'!H:H,breakfast,'Calendar year 2012_activities'!V:V,METRIC!A27)

where 'Calendar year 2012_activities'!W:W is the  idcount column ,
G:G is company name column in the activity data sheet, H:H is the activity type column in the activity data sheet, column v is the office location in the data sheet, METRICA27 is the  metric sheet cell location of the office location labels and "breakfast" is the named range i created for the meeting type label MTG- Breakfast/Lunch/Dinner (I created similar named ranges for the other meetiing type labels, too).

It worked perfectly. Thank you for your input and assitance.

Regards,
 Wasmithpfs
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now