By Kelly L. Williams, CPA, Ph.D.
As you can see in the screenshot above, Company 1690 has five entries in the spreadsheet and three group segments (with Group 21 listed three times). Similarly, companies 1691 and 1692 also contain multiple group segments.
By default, Excel will create your PivotTable in a new worksheet. You can accept that option or choose Existing Worksheet to have the PivotTable placed on the existing worksheet. If you chose Existing Worksheet, click in the cell in which you would like the PivotTable to appear. That will fill in the Location, as shown in the screenshot below.
Next, click the option next to Add this data to the Data Model, as shown in the screenshot below. This gives us some additional options we will need.
Click OK,andExcel will create a PivotTable shell to begin building the PivotTable itself. A PivotTable Fields area should open on the far-right side of your screen, as shown in the screenshot below. If the PivotTable Fields area does not open automatically, click on Analyze at the top of the screen, and click Field List from the Show group.
We would like to determine the number of group segments within each company. To do this, click and drag the word 'Company' in the PivotTable Fields area down to the Rows area, as shown in the screenshot below.
Next, click and drag the words “Group Segment” in the PivotTable Fields area down to the Values area, as shown in the screenshot below.
When you drag “Group Segment” into the Values area, by default Excel will create a Sum of Group Segment. We want a Distinct Count instead. Click the drop-down arrow next to Sum of Group Segment and choose Value Field Settings from the resulting pop-up menu. That will open the Value Field Settings dialog box, pictured to the right above. Under Summarize value field by, choose Distinct Count. If you had not previously checked Add this data to the Data Model, you will not have Distinct Count as an option. Click OK.
You should now have a list of all companies and the number of distinct group segments within each company, as shown above.![2017 2017](https://www.excelhow.net/wp-content/uploads/2017/07/excel-t-function-example1.jpg)
![How To Use Distinct Count In Excel For Mac How To Use Distinct Count In Excel For Mac](https://www.someka.net/wp-content/uploads/2016/06/how-to-use-excel-DAY-function.png)
- How To Use Distinct Count In Excel For Mac Pro
- How To Use Distinct Count In Excel For Mac Download
- How To Use Distinct Count In Excel For Mac 2017
- How To Use Distinct Count In Excel For Mac Free
Excel contains several functions to help you count the number of cells in a range that are blank or contain certain types of data. By using this site you agree to the use of cookies for analytics, personalized content and ads. Sep 22, 2019.
Q. I have a large spreadsheet that includes company codes with multiple group segments for each company. Many of the group segments within the same company are repeated. Is there a way to quickly determine how many group segments there are for each company, because my spreadsheet has thousands of lines with many companies?
A. This can be done easily with a basic PivotTable. Based on your question, I have created a simplified example to illustrate. Click here to access the accompanying spreadsheet and watch a video walkthrough at the bottom of the page.
As you can see in the screenshot above, Company 1690 has five entries in the spreadsheet and three group segments (with Group 21 listed three times). Similarly, companies 1691 and 1692 also contain multiple group segments.
How To Use Distinct Count In Excel For Mac Pro
How can you quickly calculate the number of group segments per company? To start, click anywhere within your data, go to the Insert tab on the top navigation bar, and select PivotTable from the Tables group,as shown in the screenshot above.
If you had previously clicked within your data, the entire range should already be selected in the Table/Range area in the Create PivotTable window, as shown in the red circle in the screenshot below.
By default, Excel will create your PivotTable in a new worksheet. You can accept that option or choose Existing Worksheet to have the PivotTable placed on the existing worksheet. If you chose Existing Worksheet, click in the cell in which you would like the PivotTable to appear. That will fill in the Location, as shown in the screenshot below.
Next, click the option next to Add this data to the Data Model, as shown in the screenshot below. This gives us some additional options we will need.
Click OK,andExcel will create a PivotTable shell to begin building the PivotTable itself. A PivotTable Fields area should open on the far-right side of your screen, as shown in the screenshot below. If the PivotTable Fields area does not open automatically, click on Analyze at the top of the screen, and click Field List from the Show group.
We would like to determine the number of group segments within each company. To do this, click and drag the word 'Company' in the PivotTable Fields area down to the Rows area, as shown in the screenshot below.
Next, click and drag the words “Group Segment” in the PivotTable Fields area down to the Values area, as shown in the screenshot below.
When you drag “Group Segment” into the Values area, by default Excel will create a Sum of Group Segment. We want a Distinct Count instead. Click the drop-down arrow next to Sum of Group Segment and choose Value Field Settings from the resulting pop-up menu. That will open the Value Field Settings dialog box, pictured to the right above. Under Summarize value field by, choose Distinct Count. If you had not previously checked Add this data to the Data Model, you will not have Distinct Count as an option. Click OK.
You should now have a list of all companies and the number of distinct group segments within each company, as shown above.
About the author
Usb av grabber pro driver. Kelly L. Williams, CPA, Ph.D., MBA, is an assistant professor of accounting at Middle Tennessee State University.
Submit a question
![2017 2017](https://www.excelhow.net/wp-content/uploads/2017/07/excel-t-function-example1.jpg)
How To Use Distinct Count In Excel For Mac Download
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to [email protected]. We regret being unable to individually answer all submitted questions.
SPONSORED REPORT Doblon siglos karaoke professional 1.2.55 torrent.
Supercharge your audit process with AI
Auditors today can employ AI to automate tedious tasks and gain far greater insights from their clients’ information. This free report lays out a five-step process for implementing AI and shows ways AI can add value to the auditing process.
How To Use Distinct Count In Excel For Mac 2017
![How To Use Distinct Count In Excel For Mac How To Use Distinct Count In Excel For Mac](https://www.someka.net/wp-content/uploads/2016/06/how-to-use-excel-DAY-function.png)
How To Use Distinct Count In Excel For Mac Free
RESOURCES
Keeping you informed and prepared amid the coronavirus crisis
We’re gathering the latest news stories along with relevant columns, tips, podcasts, and videos on this page, along with curated items from our archives to help with uncertainty and disruption.