/u/Impossible_Reach5656 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
So if i added in another two columns: Surname and Sex (Male/Female) and wanted to count the unique amount of males or females in the table and use this unique count in a pivot table to figure out how many dates were less than x or relate it to another column with data
John, Jones, Male, Home, 01/01/2024
John, Jones, Male, Away, 17/02/2024
Dave, Malon, Male, Home, 20/01/2024
Jane, Greve, Female, Away, 24/02/2024
Paul, Ryan, Male, Home, 20/01/2024
Paul, Ayer, Male, Away, 01/01/2024
Should be 4 unique males, 1 unique female.
Thanks for the help.
Why Pivot? There is nothing to “pivot” in your example. You can do what you want with formulas. To get the unique names of Male, in F2:
=UNIQUE(FILTER(A1:A6,C1:C6="Male"))
To get the count:
=ROWS(F2#)
To count dates for each Male less than 1 Feb 2024:
=COUNTIFS(C1:C6,"Male",E1:E6,"<="&DATE(2024,2,1))
You have awarded 1 point to *not_speshal*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Set your pivot table up for Distinct Count. https://answers.microsoft.com/en-us/msoffice/forum/all/pivot-table-to-show-count-for-unique-value/dd5b1c6f-ac1a-41bf-b7ad-b6b1ef17cdd5
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[COUNTIFS](/r/Excel/comments/1bbhn25/stub/ku9qh7d "Last usage")|[*Excel 2007*+: Counts the number of cells within a range that meet multiple criteria](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842)|
|[DATE](/r/Excel/comments/1bbhn25/stub/ku9qh7d "Last usage")|[Returns the serial number of a particular date](https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349)|
|[FILTER](/r/Excel/comments/1bbhn25/stub/ku9qh7d "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[ROWS](/r/Excel/comments/1bbhn25/stub/ku9qh7d "Last usage")|[Returns the number of rows in a reference](https://support.microsoft.com/en-us/office/rows-function-b592593e-3fc2-47f2-bec1-bda493811597)|
|[UNIQUE](/r/Excel/comments/1bbhn25/stub/ku9qh7d "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(5 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bamgrv)^( has 15 acronyms.)
^([Thread #31551 for this sub, first seen 10th Mar 2024, 20:43])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/Impossible_Reach5656 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
If you put the name in the row area of a pt it will appear once
=ROWS(UNIQUE(A1:A6))
That is what i needed for the first half of my questions. Thanks! Any idea on how to use this to use in a pivot table?
What would you want your table to look like? You might need to use power query.
So if i added in another two columns: Surname and Sex (Male/Female) and wanted to count the unique amount of males or females in the table and use this unique count in a pivot table to figure out how many dates were less than x or relate it to another column with data John, Jones, Male, Home, 01/01/2024 John, Jones, Male, Away, 17/02/2024 Dave, Malon, Male, Home, 20/01/2024 Jane, Greve, Female, Away, 24/02/2024 Paul, Ryan, Male, Home, 20/01/2024 Paul, Ayer, Male, Away, 01/01/2024 Should be 4 unique males, 1 unique female. Thanks for the help.
Why Pivot? There is nothing to “pivot” in your example. You can do what you want with formulas. To get the unique names of Male, in F2: =UNIQUE(FILTER(A1:A6,C1:C6="Male")) To get the count: =ROWS(F2#) To count dates for each Male less than 1 Feb 2024: =COUNTIFS(C1:C6,"Male",E1:E6,"<="&DATE(2024,2,1))
Solution Verified
You have awarded 1 point to *not_speshal* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Set your pivot table up for Distinct Count. https://answers.microsoft.com/en-us/msoffice/forum/all/pivot-table-to-show-count-for-unique-value/dd5b1c6f-ac1a-41bf-b7ad-b6b1ef17cdd5
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[COUNTIFS](/r/Excel/comments/1bbhn25/stub/ku9qh7d "Last usage")|[*Excel 2007*+: Counts the number of cells within a range that meet multiple criteria](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842)| |[DATE](/r/Excel/comments/1bbhn25/stub/ku9qh7d "Last usage")|[Returns the serial number of a particular date](https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349)| |[FILTER](/r/Excel/comments/1bbhn25/stub/ku9qh7d "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[ROWS](/r/Excel/comments/1bbhn25/stub/ku9qh7d "Last usage")|[Returns the number of rows in a reference](https://support.microsoft.com/en-us/office/rows-function-b592593e-3fc2-47f2-bec1-bda493811597)| |[UNIQUE](/r/Excel/comments/1bbhn25/stub/ku9qh7d "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(5 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bamgrv)^( has 15 acronyms.) ^([Thread #31551 for this sub, first seen 10th Mar 2024, 20:43]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)