Counting Unique Values

1. 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

2. 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.

3. 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. 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.

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