Excel Magic Trick 1153: Unique Count For Name & Date: Array Formula, 2013 PivotTable, More…




About this tutorial:

Video duration: 10:25
Download Excel File:
See how to:
1. (00:08 minute mark) Problem Set Up
2. (00:55 minute mark) Excel 2013 PivotTable and “Add to Data Model” to get Distinct Count Formula
3. (02:38 minute mark) Remove Duplicates, Screen Tip Trick and COUNT function
4. (03:42 minute mark) Array Formula with MATCH, Concatenated Columns, FREQUENCY Array Function, IF and SUM function. Learn that because we use FREQUENCY function to generate Array, we do not need Ctrl + Shift + Enter when we put FREQUENCY in IF and SUM functions.

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

8 thoughts on “Excel Magic Trick 1153: Unique Count For Name & Date: Array Formula, 2013 PivotTable, More…

    Shekar N

    (July 10, 2018 - 2:34 pm)

    shku unique ID

    Shekar N

    (July 10, 2018 - 2:34 pm)

    I want unique ID in names like. example Shekar Kumar. I want unique ID

    Mehar Ali

    (July 10, 2018 - 2:34 pm)

    Dear I Have a problem regarding counting formula. please help me.
    I want to search a unique count with date for example
    A1:A50 relevant with verious name and in column B1:B50 there is mentioned dates.
    I want to get the result of unique name of required date. like how many names falls in the given date. Is it possible.
    Regards,
    Mehar Ali Khatri

    Denis Gradišnik

    (July 10, 2018 - 2:34 pm)

    Hi I don't know if this is the right video or not but I'm going to submit my question here
    I have 300 data in dates ranging from 2016 do 2004, and I want co count how many of this dates is from 1.1.2016 to 31.12. 2016 (one year), and ten for each year I want t the count how can I do this .

    Bill Szysz

    (July 10, 2018 - 2:34 pm)

    Great solutions !!!
    Thanks :-))

    Tim Sheets

    (July 10, 2018 - 2:34 pm)

    Great video!  I need to read up on the frequency function…didn't really understand that part.

    Kotara

    (July 10, 2018 - 2:34 pm)

    What if I would like to add criteria to that formula? If there was a 3rd column with a category for example and I would want to know how many unique values I have for each one of them?

    ExcelIsFun

    (July 10, 2018 - 2:34 pm)

    Excel Magic Trick 1153: Unique Count For Name & Date: Array Formula, 2013 PivotTable, More…
    Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm
    See how to:
    1. (00:08 minute mark) Problem Set Up
    2. (00:55 minute mark) Excel 2013 PivotTable and “Add to Data Model” to get Distinct Count Formula
    3. (02:38 minute mark) Remove Duplicates, Screen Tip Trick and COUNT function
    4. (03:42 minute mark) Array Formula with MATCH, Concatenated Columns, FREQUENCY Array Function, IF and SUM function. Learn that because we use FREQUENCY function to generate Array, we do not need Ctrl + Shift + Enter when we put FREQUENCY in IF and SUM functions.

Leave a Reply

Your email address will not be published. Required fields are marked *