Forum Discussion
Counting Distinct values, number and text
I have a series of "master tables" worksheets, each divided by year. The tables have approx 15 columns, each unique purpose that is imported from another excel sheet.
I need to extrapolate data then develop charts for a dashboard for analysis and evaluations.
I created a table in each years worksheet for the extrapolated data and need to have automated formulas to fill the cells the graphs populate with.
I'm using MS Excel 2016 professional, but it needs to run on 2013.
I can calculate the "Course" data easy enough, but I CANNOT figure out how to do this for "Members". There's multiple categories that each could be counted in. I've tried using versions of UNIQUE, FREQUENCY, COUNTIF, etc
I must have accurate data and each cell must have only the distinct data that meets from 1 to 4 criteria. I also need to be able to count data that has a 2, 3, 4, or 5 as well as meets multiple school codes, but I only need the "Distinct" number and not the total occurrences.
I need an efficient formula since the many worksheets will have in excess of 60,000 rows/records.
I've included a sample worksheet with the same format dummy data and some notes to help my quandary.
I have a short date fuse on this and hope someone can help!!! If needed, I'm open to have a voice or TEAMs chat for questions.
8 Replies
It's been seven hours, over 35 views.... your short fuse might just burn down to the nub.
The spreadsheet you have posted is perhaps clear to you in terms of how to navigate its various sections, but isn't all that clear to somebody seeing it for the first time.
I am going to guess that everybody else who has looked at it and not responded has had the same reaction; I'm just doing the courtesy of letting you know....if you want help, you're going to need to make the organization of your sheet a LOT clearer.
It might benefit from having the extensive database on one sheet by itself, anchored in the upper left hand corner. Then take the various areas meant to extract and analyze the database, to produce those desired results, and place them on their own sheet, and there too not separated by all kinds of white space.
You might even benefit from being able to see things more clearly. You certainly would improve your chances of getting a more substantive reply.
I thank you for the guidancemathetes . I've not posted for help before and appreciate the kind words as it has made my 20+ years in the Marines much brighter... 😉 Seriously though, I'll adjust the data but I don't understand what "anchoring in the upper left hand corner" would do to help.... but I'll try.
Gregg
"Anchoring in the upper left corner" was simply my way of saying "place the table there" so that it's immediately apparent when you open up the sheet. As it is, one has to go hunting. For each thing on your sheet. It's kind of a nightmare to anyone who wasn't part of its creation. And my guess is that you find it frustrating to navigate as well. One of the true values of using multiple sheets is the advantage of being able to find your way around each without much trouble. Make each sheet more dedicated to at most a few tasks or functions. You can always link across sheets........ This is especially true if you're going to be asking for help.
