Excel Magic Trick 1053: Unique Count With Criteria: Excel 2013 PivotTable Distinct Count Function

Amazing New Excel 29013 feature to count unique items with criteria:
1. In Excel 2013 the New PivotTable Function Distinct Count can replace complicated Array Formulas.
2. The Distinct Count function in a 2013 PivotTable will ONLY APPEAR if you check the "Add this data to Data Model" in the Create PivotTable dialog box.
3. The Data Model is part of the PowerPivot engine and rthis works EVEN if you don't have PowerPivot.
4. Once you create your pivot table: 1) Drop field you want to make a unique count upon into the Values area, then 2) drop field with the criteria into the Row area and/or use filter.
5. New Keyboard For PiviotTable Excel 2013: Alt N, V

Excel Unique Count, Unique Count Function In Excel 2013

[excelisfun]

Yes, I saw that video - it was awesome!!!

What an amazing Online Excel team we have! :)

[om]

This is great!!!! Finally we have distinct count in Pivot Tables!!
Thanks Mike!

[MikeDolanFliss]

Wow wow wow does this make my life easier. Thanks Mike.

[excelisfun]

SUMIFS or Pivot on Date field. Here is Pivot video:

Slaying Excel Dragons Book #40: Grouping Dates in PivotTables: Years, Months or Weeks, and more

Here is SUMIFS video:

Excel Magic Trick 793: Add Between Two Dates/Times/Numbers SUMIF & SUMIFS 10 Examples

Here is playlist with many videos about monthly, yearly reports:

youtube [dot]

Read through the titles in this playlist cuz it has many examples that may apply to your specific situation.

[ExcelStrategy]

That is an amazing new feature in Excel 2013 ! I have also made a cool video on the same subject some time ago :)

[ThePowerUser]

Yup! but you need to download the Power Pivot add-in

[anupamav]

Hi there, I would like to know if the "count" field in the pivot table can be used in a calculated field. All the examples I have seen so far only shows the "Sum" fields being used in calculated field. Appreciate help. Thanks.

[excelisfun]

For back and firth dialog to get Custom Excel solutions, try THE best Excel question site:

mrexcel. com/forum

[reyramirez]

Hello. I was wondering if it is at all possible to have countifs formulas function as a pivot table? I have a workbook that I use with new data on a daily basis so I have set it up with formulas but it would be great if I could double click the formula result and have it show the details. I can't make a pivot table with it because I set up the workbook to have custom criteria entered. the workbook has about 20 custom tables in it as well. 

[antoniopedro]

Thanks for the video! is it possible to group the dates into months or years? I've tried to count the number of different products that I buy per month but I cannot do it using the pivot table... thanks in advance

[kaitlynstormes]

I do not see the options to add to model. Can you advise?

[jennypetzer]

Hallo can u help me the problem I have is to count all the bv -R and +R in a columns in different cells and the -R and +R is changing all the time

[justine.vander]

Does anyone know how you can get a distinct count on a macbook? I do not have the "Add this data to the data model" button and therefore, I cannot get a distinct count. Any suggestions would be greatly appreciated! :)

[jessicachen]

I found that I was not able to expand my source date after I chose "Add this data to the data model" check box. For example, I added several rows to my source data, and clicked on the "change data source" as usual, then my pivot table just disappeared after I expanded my sources to include the new rows. I had to drag fields again. Anyone has suggestions on that?

[bartektlu]

Butt...There is NO DISTINCT COUNT option when you create pivot table report based directly from sql server table. Can Anybody tell me why? A graet disappointment fror me.
I bought excel 2013 only because of the tip.