+ Reply to Thread
Results 1 to 4 of 4

Counting Unique Values

  1. #1
    Registered User
    Join Date
    07-27-2020
    Location
    Pennsylvania
    MS-Off Ver
    2016
    Posts
    13

    Counting Unique Values

    I am building a participation tracking system and got stuck. We have RFID badges that can be scanned into Excel to track attendance. I have set up that part with no problem. I am trying to set up a dashboard to easily see attendance. I currently have all scans going to a table so I can turn them into pivot tables for the dashboard. However, since my system tracks every instance of attendance a value is counted more than once. Example, 1 person attends an exercise class on 7/27/2020 and then attends the same class again on 7/29/2020. It will show up that the resident attended the class twice, which is correct and I want to track that. However, I also want it to show that the 1 person has attended the class from a historical perspective. I need help to be sent in the right direction for formulas so that I can 2 things: There were a total of 10 attendance for this class and it was attended by 4 different people.

    I know this can be completed using countif and sumproduct but I want this to be shown on 1 single dashboard that is able to be filtered by slicers.

    Thank you
    Last edited by jrod59; 07-30-2020 at 09:52 AM.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,234

    Re: Counting Unique Values

    Hello jrod59 and Welcome to Excel Forum.
    It may help someone to better understand if we could see a sample .xlsx file, instructions are given in the banner at the top of the page.
    Remember to manually produce the results that you would like to see automated in the dashboard.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    07-27-2020
    Location
    Pennsylvania
    MS-Off Ver
    2016
    Posts
    13

    Re: Counting Unique Values

    I have an attendance tracking system using RFID scanners. We want to now be able to look a distinct counts of data and not just the total values. Example, I want to filter the data to look at the results of only Males. If I do this in a pivot table using slicers I receive the results in side A, which I want as it shows the total amount of visits on each day of the week. However, I also want to see the unique number of males who attended on each specific day of the week. So Side A says we had 2 visits by males on Tuesdays and Side B says we only had 1 male visit on Tuesday.

    Any suggestions on the formulas? This is for a tracking spreadsheet with thousands of rows.



    I cant seem to get my sample doc to attach so I typed it out

    Resident Name Gender Age Date Range Day of Week
    John, John Male 70-79 6/13/2020 Monday
    Steve, Steve Male 60-69 6/14/2020 Thursday
    Mike, Mike Male 60-69 6/16/2020 Monday
    John, John Male 70-79 6/18/2020 Tuesday
    Mike, Mike Male 60-69 6/18/2020 Sunday
    John, John Male 70-79 6/19/2020 Sunday
    John, John Male 60-69 6/21/2020 Monday
    Steve, Steve Male 70-79 6/23/2020 Wednesday
    John, John Male 60-69 6/23/2020 Tuesday
    Mike, Mike Male 70-79 6/25/2020 Monday


    Side A (total visits) (I already have this)
    Day of Week
    Sunday 2
    Monday 4
    Tuesday 2
    Wednesday 1
    Thursday 1
    Friday 0
    Saturday 0


    Side B (Visits by unique # of males on each day of the week) (This is want I am looking for)
    Day of Week
    Sunday 2
    Monday 3
    Tuesday 1
    Wednesday 1
    Thursday 1
    Friday 0
    Saturday 0

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,234

    Re: Counting Unique Values

    Common reasons that an .xlsx file will not upload:
    1. Attempting to use the paperclip icon (it does not work)
    2. File is more than 1000 KB
    If your issue is due to reason #1, please follow directions in the banner, however if the file is too large to upload you could either reduce the amount of data or upload as a .zip (up to 9.77 MB)
    Let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] counting unique values in col A against unique value in column B
    By greyscale in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2013, 08:43 AM
  2. Replies: 2
    Last Post: 06-06-2012, 01:44 PM
  3. Replies: 0
    Last Post: 03-08-2012, 12:18 PM
  4. counting unique values
    By saimike in forum Excel General
    Replies: 5
    Last Post: 11-01-2011, 01:12 PM
  5. Counting unique values
    By giantwolf in forum Excel General
    Replies: 3
    Last Post: 08-04-2006, 03:54 AM
  6. [SOLVED] Counting Unique Values
    By carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. Counting Unique Values
    By carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  8. [SOLVED] Counting Unique Values
    By carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1