?
Solved

Countifs to count distinct values

Posted on 2013-01-14
8
Medium Priority
?
633 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 46

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 46

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
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!

 

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 46

Accepted Solution

by:
aikimark earned 2000 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 46

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

762 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