Forum Discussion
Counting Distinct values, number and text
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.
mathetes, you're right on the frustration at times. I appreciate the insight and advice. I hope someone can assist me in a formula to count the distinct data vice "hand jamming" and calculating the info for each year. the master workbook is already 400mb with 30+ sheets. Maybe thats small to some, but its BIG to me.
I'm going to have to beg your patience here. At present I'm away from my home base, where I have two screens to work with, each quite a bit larger than the screen on my laptop. And tackling your issue is going to require me to take full advantage of the larger real estate on those two screens. Perhaps somebody else can hop in in the meantime. I expect to be at home toward the end of the day today.
Also in the meantime, if I might: In your very first post you wrote:
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
And there are at least two things there that concern me with regard to the assistance you're seeking.
- First, your statement that "it needs to run on 2013" suggests that you're creating a sheet that will be used by others (which underscores the necessity for clear design, first of all) on older versions of Excel, and
- Second, THAT means they won't have access to functions like UNIQUE or FILTER, which are only available in the most recent releases. That kind of puts some limitations on the solutions that are possible.
On a more positive note, it does occur to me that some very old and less frequently used functions might serve your purpose. So you might want to poke around in them while you wait. And if indeed we must create a solution that works with Excel 2013, this may be the most workable solution. Excel has had for at least a couple of decades a set of Database functions. They're the ones that begin with the letter D, and include such things as DSUM, DGET, DAVERAGE, DCOUNT.... There's a bit of a learning curve to mastering them, but they provide the kind of data extraction that you appear to be seeking. DCOUNT in particular. I've never used it, but I have used (in fact am using currently) DGET in a personal budget and expense tracking sheet. I've used DSUM recently in an invoicing system I created for my wife's consulting, yielding different figures for work done during any given month in each of a number of project categories, extracting that data from a table she maintained to record hours worked on each project by date.
Here's a link to a website that might point you toward how you could use those database functions and solve this on your own.
https://exceljet.net/search?query=database
I will say that the trickiest part for what you're trying to do may be figuring out a way to array the criteria so that the DCOUNT results get displayed in the table(s) where you want them. But I do think it could work.
It might be that Power Query could address your concerns--but that is a capability that others will have to address as I've not delved into its intricacies.