Solved

Countifs to count distinct values

Posted on 2013-01-14
8
610 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
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

679 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