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

preview_player
Показать описание


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.
  1. excelisfun
  2. Excel 2013
  3. Excel 2003
  4. Excel 2007
  5. Excel 2010
  6. excelisfun
Рекомендации по теме
Excel Magic Trick 0:10:25

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

Excel Magic Trick 0:06:09

Excel Magic Trick 1154: Unique Count:Name & Date & Criteria From 3rd Column (3 Methods)

Excel Magic Trick 0:04:26

Excel Magic Trick 1159: Extract Unique List For Data Validation List with Power Query

Excel Magic Trick 0:07:30

Excel Magic Trick 1152: Formula or Power Query: Cost Report When Product IDs Don’t Match

Excel Magic Trick 0:12:38

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

How to find 0:01:14

How to find unique values Excel

Excel Magic Trick 0:12:20

Excel Magic Trick 1149: Dynamically Sum by Category without VLOOKUP Helper Column (2 methods)

Excel Magic Trick 0:06:00

Excel Magic Trick 1540: Extract Unique List of Dates From a Date-Time Column

Excel Magic Trick 0:06:11

Excel Magic Trick 1156: Count with Two Conditions / Criteria From Non-Proper Data Set (2 Methods)

Excel Magic Trick 0:04:53

Excel Magic Trick 1477: COUNTIFS & AND Functions for Duplicate Event Codes AND Task = 'Spec...

Excel Magic Trick 0:10:33

Excel Magic Trick 1192: Match Lookup Value to Closest Number in 1st Column of Lookup Table

Excel Magic Trick 0:09:41

Excel Magic Trick 1027: Array Formula To Count Unique Yes Votes For A Given First & Last Name

Count Unique Values 0:04:32

Count Unique Values Excel

How to Extract 0:02:06

How to Extract Unique items from the list in Excel

Excel Magic Trick 0:13:46

Excel Magic Trick 1453 Array Formula Count Customer Totals Between Upper Lower Limits, Each Month

Excel 2013 Count 0:00:37

Excel 2013 Count Distinct Values Including Blanks

Excel Magic Trick 0:05:07

Excel Magic Trick 1157: Running Total Jumps Over Hurdle & Gas Cost for Mileage is Reduced Formul...

Lookup 2nd Smallest 0:06:38

Lookup 2nd Smallest Number. 2nd Biggest too. UNIQUE or FREQUENCY function? Excel Magic Trick 1672

Excel Magic Trick 0:07:14

Excel Magic Trick 1236: Count Numbers that Contain Whole Number 4: COUNTIFS or SUMPRODUCT?

Excel Magic Trick 0:07:51

Excel Magic Trick 1164: Average Score After Subtracting Two Lowest: SMALL & Array or AVEAGEIF?

Excel Magic Trick 0:10:35

Excel Magic Trick 1072: Conditionally Format Partial Row Based On Start Month, End Month & Produ...

Extract Unique Dates 0:01:44

Extract Unique Dates From A List In Excel

Excel Magic Trick 0:01:46

Excel Magic Trick 1146: Create ISO Dates using TEXT function and Custom Number Format

How to Count 0:00:52

How to Count Unique Items in Excel PivotTables

Комментарии
Автор

Excel Magic Trick 1153: Unique Count For Name & Date: Array Formula, 2013 PivotTable, More…
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.

excelisfun
Автор

Thanks for the video. Really helped me out. Is there a way to do the count for a NamedRange where I have included the whole column so that it automatically updates the count when a value is entered into the next cell? Currently I get an N/A if I use either a NamedRange for the whole column or just $Column :$column in the formula. Thanks again for the tutorial!

skiphouston
Автор

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?

MlKotara
Автор

What an awesome and detailed tutorial, i am stuck in a similar kind of a issue but a little different. Hope to get some help when saw this video so thought of requesting for help.

In my bump I have huge data column A is names which has multiple duplicates and column B has dates again multiple duplicates. In sheet 2 is it possible to find the count of unique dates for each unique name using a formula....

Diamond-Daga
Автор

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

TimSheets
Автор

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

ahmedraza
Автор

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 .

anxiousDenis
Автор

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

shekarn