Solved

Countifs to count distinct values

Posted on 2013-01-14
8
599 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
Comment Utility
@wasmithpfs

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

Author Comment

by:wasmithpfs
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:wasmithpfs
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now