Excel Magic Trick 1318: Unique Visitors To Site Each Day? Data Model PivotTable or Array Formula?

Download Excel Files:

See how to use calculate Distinct Count with 1 Condition to create report and Chart for How Many Unique Visitors To Site Each Day with Two Methods:
1. (00:13) Introduction
2. (01:01) Excel 2016 Data Model PivotTable and Distinct Count Function.
3. (05:43) New Blue Cover Ctrl Shift Enter Book
4. (06:09) Array Formula with SUM, IF, FREQUENCY, ROW and MATCH functions.

Distinct Count Formula with one condition. Unique Count Formula with one condition.

[robertbloomfield]

Been trying to figure these formulas out all day, watched your video and got the data I need in two minutes! Great tips!

[josebetancourt]

Great video!! But instead of using row minus row, is possible to use ROWS function locked on $A$7 down to the end?

[dericcheung]

Can you use "counifs" to simplify the above formula and to avoid control shift enter ?

[Victor-ollo]

Hi Mike! Great video !!! Thumbs up !! I may have an interesting topic for one of your next videos -> How to calculate a moving average based on weeks or months in powerpivot or pivot? Have not found any simple solution yet....

[jazzista]

Amazing video Mike: Quick question/clarification : I thought that you can use the frequency function only when working with numbers instead of text? So can the frequency function can also handle both text and numbers. Thanks.

[BillSzysz]

Thanks for impressive video !!
Do you know that we can do this in older version of excel (2010, 2007) with two Pivot Tables (there is no "Add to the Data Model" option and no "distinct count" in PT). One of them is ordinary PT and the second should be created by PT wizard.
PQ will be also very good choise to do this ( very simple in it).

[planiolro]

How can we order the latest version of the book Ctrl+Shift+ Enter. I would be interested about the ebook since I am outside USA.

[gdwfs]

Brilliant. You should be working for NASA or Goldman Sachs!

[kjvstats]

Mike, I really enjoyed this great comparison video. You might be doing yourself out of a job if folks start switching to PQ and not learning the nested formulas that you've mastered. Thanks.

[Sal_A]

FREQUENCY for unique count is great, just as I have learned from your book. We could do this for this specific example too (but it's not as dynamic if duplicate names are not above each other):
=SUMPRODUCT(--($A$7:$A$26=F7), --($D$7:$D$26<>$D$8:$D$27))

[riptorforever]

Hello, Mike! Are you fine? I hope so!
This is a tip to extend the reach of your videos:
First of all, two considerations:
#1 Currently the channel has subtitles in English for most videos.
#2 The tool of caption translate from youtube is very failure, mainly because the videos having many technical terms.
The tip: It would be very useful to be activated function "contribute closed captions", that allows viewers to produce translations of the subtitles.
In Brazil, for example, only a small margin of the population is able to understand the English language.
Some of my friends give up their videos for this reason. So, a perfect translation would be very grateful!

Bests Regards!