·
22 days ago
My Excel dont have check box " Add this data to the Data Model"
So, What can i do?
So, What can i do?
How to count unique values in pivot table?
By default, when we create a pivot table based on a range of data which contains some duplicate values, all the records will be counted as well, but, sometimes, we just want to count the unique values based on one column to get the right screenshot result. In this article, I will talk about how to count the unique values in pivot table.
Count unique values in pivot table with helper column
Count unique values in pivot table with Value Field Settings in Excel 2013 and later versions
Group date by fiscal year, half year, week number or other specific dates in pivot table
Normally, you can group the pivot table by month, year, quarter quickly, but, sometimes, you may want to group data based on the fiscal year, half-year and other specific dates. In this case, Kutools for Excel's PivotTable Special Time Grouping utility extends the original Grouping function and supports more dates grouping. Click to download Kutools for Excel!
Count unique values in pivot table with helper column
In Excel, you need to create a helper column to identify the unique values, please do with the following steps:
1. In a new column besides the data, please enter this formula =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) into cell C2, and then drag the fill handle over to the range cells that you want to apply this formula, and the unique values will be identified as below screenshot shown:
2. Now, you can create a pivot table. Select the data range including the helper column, then click Insert > PivotTable > PivotTable, see screenshot:
3. Then in the Create PivotTable dialog, choose a new worksheet or existing worksheet where you want to place the pivot table at, see screenshot:
4. Click OK, then drag the Class field to Row Labels box, and drag the Helper column field to Values box, and you will get the following pivot table which just counts the unique values.
Count unique values in pivot table with Value Field Settings in Excel 2013 and later versions
In Excel 2013 and later versions, a new Distinct Count function has been added in the pivot table, you can apply this feature to quickly and easily solve this task.
1. Select your data range and click Insert > PivotTable, in the Create PivotTable dialog box, choose a new worksheet or existing worksheet where you want to place the pivot table at, and check Add this data to the Data Model checkbox, see screenshot:
2. Then in the PivotTable Fields pane, drag the Class field to the Row box, and drag the Name field to the Values box, see screenshot:
3. And then click the Count of Name drop down list, choose Value Field Settings, see screenshot:
4. In the Value Field Settings dialog, click Summarize Values By tab, and then scroll to click Distinct Count option, see screenshot:
5. And then click OK, you will get the pivot table which count only the unique values.
- Note: If you check Add this data to the Data Model option in the Create PivotTable dialog box, the Calculated Field function will be disabled.
More relative PivotTable articles:
- Apply The Same Filter To Multiple Pivot Tables
- Sometimes, you may create several pivot tables based on the same data source, and now you filter one pivot table and want other pivot tables are filtered with the same way as well, that means, you want to change multiple pivot table filters at once in Excel. This article, I will talk about the usage of a new feature Slicer in Excel 2010 and later versions.
- Group Date By Month, Year, Half Year Or Other Specific Dates In Pivot Table
- In Excel, if the data in a pivot table includes date, and have you tried to group the data by month, quarter or year? Now, this tutorial will tell you how to group date by month/year/quarter in pivot table in Excel.
- Update Pivot Table Range In Excel
- In Excel, when you remove or add rows or columns in your data range, the relative pivot table does not update at the same time. Now this tutorial will tell you how to update the pivot table when rows or columns of the data table change.
- Hide Blank Rows In PivotTable In Excel
- As we know, pivot table is convenient for us to analyze the data in Excel, but sometimes, there are some blank contents appearing in the rows as below screenshot show. Now I will tell you how to hide these blank rows in pivot table in Excel.
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment...
The comment will be refreshed after 00:00.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
· 1 months agoThank you so much !!!!!
-
To post as a guest, your comment is unpublished.
· 3 months agoI cannot edit after I save. Can yo tell me why?
-
To post as a guest, your comment is unpublished.
· 4 months agosorry, this still doesn't provide a solution for me in excel 2010. You're =if(sumproduct() formula doesn't work. It misses the values for the if formula if you use it like you put it and it doesn't count unique values in my excel sheet if I add =if(>1,01;1;0)...
-
To post as a guest, your comment is unpublished.
· 4 months agooh man... you saved me so so so much time !!!
thanks a lot !!!! -
To post as a guest, your comment is unpublished.
· 1 years agoDistinct count Option not shown in summarize value by - Excel version 2013
-
To post as a guest, your comment is unpublished.
· 1 years agoPlease verify that you have ticked the "Add this data to data model" check in the CreatePivot dialog box :) -
To post as a guest, your comment is unpublished.
· 1 years agoI faced the same issue and then found the resolution.
Seems that it's available only when you tick the "Add this data to the Data Model" checkbox in the Create PivotTable dialog box.
Please try if that helps -
To post as a guest, your comment is unpublished.
· 1 years agosame for me! Any suggestion?
-
-
To post as a guest, your comment is unpublished.
· 1 years agoThese all work but only to an extent. I'm trying to find a solution for the issue with all of these. When I create a helper column and use the formula =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) I do indeed get the distinct count. But how do you resolve the issue were you need the pivot fields to include one of the lines of data where the formula gives a zero? I also tried using the Data Model and distinct count. This gives the correct count but when you double click the data to drill down you do not get the data specified in the pivot.
-
To post as a guest, your comment is unpublished.
· 1 years agoAmazing! thanks a tons - this worked for me on Excel 2016.
-
To post as a guest, your comment is unpublished.
· 1 years agoI don't see the Distinct Count under Summarize Value By tab. My "Add this data to the Data model" check box is also grey out. How can I change this setting?
-
To post as a guest, your comment is unpublished.
· 5 months agoRan into the same issue... it is probably because the file you opened was as a csv. When I reopened my file as an excel file (either start a new one, copy+paste or save as), I have the functionality of adding to data model
-
-
To post as a guest, your comment is unpublished.
· 1 years agoomg!!! yes...thanks for this!!!!
-
To post as a guest, your comment is unpublished.
· 1 years agoThanks. It is helpful..
-
To post as a guest, your comment is unpublished.
· 1 years agoThank you. saved lot of hours.
-
To post as a guest, your comment is unpublished.
· 1 years agoI am using excel 2016 but I am not seeing the Count Distinct option in the pivot Value Fields Settings window. There are only Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var and Varp. Any thoughts on how to find it?
-
To post as a guest, your comment is unpublished.
· 1 years agoHello, Nick,
You should check Add this data to the Data model check box in the first step when you creating the pivot table, see screenshot:-
To post as a guest, your comment is unpublished.
· 1 years agoHi Skyyang, Thank you, I did select this but once it is selected, I am not able to add calculated fields. Do you know how to add in calculated fields using this method?
-
To post as a guest, your comment is unpublished.
· 1 years agovery helpful!
-
-
-
To post as a guest, your comment is unpublished.
· 1 years agoSo glad I came across this.
-
To post as a guest, your comment is unpublished.
· 1 years agoVery helpful !
-
To post as a guest, your comment is unpublished.
· 1 years agoNever used that Add this data to the data model before, great tip! Thanks!
-
To post as a guest, your comment is unpublished.
· 1 years agoAwesome ... thank you.
-
To post as a guest, your comment is unpublished.
· 3 years agoI own and love KuTools, but to find unique values (using 2010) whether with helpers cells or Kutools, do does the data have to be sorted so that the unique field can be found? Thank you.
English
العربية
简体中文
繁體中文
Nederlands
Français
Deutsch
Italiano
日本語
한국어
Português
Русский
Español