Such a clear question originating from the classic: “How many unique items per group“?

how-many-unique-visitors-to-site-each-dayv2

A few days ago Mike Girvin (ExcelIsFun) created several videos explaining different ways to solve this. See a couple of my ideas further down.

 

Let’s Take A Look At The Sample Data.

data-sample_how-many-unique-visitors-per-day

With this small sample we see that:

  • 8/29/2016 has 4 unique visitors
  • 8/30/2016 has 6 unique visitors
  • 8/31/2016 has 4 unique visitors

Note that ‘Time IN’ and ‘Time OUT’ columns aren’t required for solving our question.

 

What’s The Best Way To Solve This?

My goofy doodle below summarizes Mike’s solutions. Further down you’ll see my helper column solution and an alternative array formula solution.

nerds-possible-solutions-to-count-unique-items-per-group

 

What About A Simple PIVOT TABLE?

Data Model Solution

A traditional Pivot Table can’t solve this because there’s no distinct count function. The count function counts all employees per date including the duplicates. However, when creating a new Pivot Table we can check “Add this data to the Data Model” which displays a Distinct Count function!

Watch Mike Girvin’s video

This is almost a perfect solution and should be the end of the story. You’ll probably love it but some people decide to skip this method. Why? They aren’t comfortable with Excel’s Data Model or don’t like losing minor Pivot features like ‘Grouping’.

Pivot Built On Pivot Solution

This sounds a bit odd but it’s a super fast method to solve our unique items per group question. Mike got the idea from Bill Szysz video (in Polish).

GET & TRANSFORM?

Get & Transform is an amazing data tool! It is a built-in feature in Excel 2016 (available as add-in in Excel 2010 and 2013) that allows you to re-arrange your data. Watch Mike’s video to see how easy it is to solve the unique items per group question.

Watch Mike Girvin’s video

 

What About FORMULA Solutions?

If your data changes a Pivot Table solution requires a data refresh where as formulas do not! Yes, this is an advantage but formula based solutions are much more complex. Think of formula solutions as ‘cooking from scratch’ while Pivots are like packaged quick meals that require minimal preparation.

Array Formulas

=SUM(IF(FREQUENCY(IF($D$7:$D$26<>””,IF($A$7:$A$26=K7,MATCH($D$7:$D$26,$D$7:$D$26,0))),ROW($D$7:$D$26)-ROW($D$6)),1))
I love array formulas as they can do magic but they take time to understand. If you copy/paste Mike’s formula above into Excel don’t forget to press Control Shift Enter (instead of Enter).
Watch Mike Girvin’s video
   

are-there-any-other-ways-to-solve-this

Watch my entire YouTube video or only the method that you’re interested in below.

Method 1: Quick & Dirty REMOVE DUPLICATES

remove-duplicates

If you only have a few records then consider using Excel’s Remove Duplicates feature (YouTube Video).

 

Methods 2 & 3: Combine Helper Column with Sumif or Countif

no-array-or-pivot-table

Method 2: If arrays make you queasy and you don’t want to refresh a Pivot Table then consider a helper column with a simple formula like this =1/COUNTIFS($A$7:$A$26,A7,$D$7:$D$26,D7) followed by a Sumifs function (YouTube Video).

Method 3: This method uses three helper columns (a counter, a concatenated key and a match function) followed by a Countifs function (YouTube Video).

 

Method 4: Array Formula

kevins-array-formula

So…I wondered if I could take the 1/COUNTIFS helper column idea and convert it into a single cell array formula. Eventually I got this to work and gave myself a high five! My dog Cali didn’t seem to be impressed.

My Array Formula:

=SUM(IFERROR(1/COUNTIFS($A$7:$A$26,K7,$A$7:$A$26,$A$7:$A$26,$D$7:$D$26,$D$7:$D$26),0))

Mike’s Array Formula:

=SUM(IF(FREQUENCY(IF($D$7:$D$26<>””,IF($A$7:$A$26=K7,MATCH($D$7:$D$26,$D$7:$D$26,0))),ROW($D$7:$D$26)-ROW($D$6)),1))

 

Are You Falling In Love With Array Formulas?

If so, there’s one thing that you have to do:  BUY MIKE’S BOOK!

cse

 

About Me

KevinInMunich_My name is Kevin Lehrbass. This is my personal blog about Microsoft Excel. I live in Markham, Ontario, Canada.  I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst at McKinsey & Company.

Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and blog posts.

Away from Excel I enjoy playing with my dog Cali, learning Spanish, playing Chess and drawing nerds.

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Post a comment