Solved

Countifs to count distinct values

Posted on 2013-01-14
8
614 Views
Last Modified: 2013-01-15
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
Comment
Question by:wasmithpfs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 38778541
@wasmithpfs

You have a circular reference at T4.  Your JERSEYRNG should be pointing to T3 instead of T4.
0
 

Author Comment

by:wasmithpfs
ID: 38778744
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
 
LVL 45

Expert Comment

by:aikimark
ID: 38778945
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
SuperAntiSpyware Licenses Discounted by 25% !

Exclusive offer to Experts Exchange Members!
Buy SuperAntiSpyware License(s) from us and save 25% on the regular purchase price.
- Includes Full SuperAntiSpyware Vendor Support Entitlements
- Your Subscription does not begin until you activate your license
- Buy for your friends

 

Author Comment

by:wasmithpfs
ID: 38779109
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
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 38779339
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
 
LVL 45

Expert Comment

by:aikimark
ID: 38779447
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
 

Author Comment

by:wasmithpfs
ID: 38780551
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question