Counting Distinct values, number and text

[GWHotmail]

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.

[#2]

@GWHotmail 

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.

[#3]

I thank you for the guidance@mathetes .  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

[#4]

@GWHotmail 

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

[#5]

@mathetesI humbly request a second chance for assistance.  I've adjusted my worksheet as advised, and like it much more.

Thanks in advance.

G

[#6]

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

[#7]

@GWHotmail 

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.

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

[#8]

@mathetes, Thanks for the push in the right direction.  I'll "D"ive into those functions right away (sorry for the pun). I'm a quick study and sleeps overrated anyways when a missions in progress.

You're spot on in the need for other to use, and working for govt results in old versions ran at work with format and planning done on newer versions at home. I appreciate your help.

Vr

G

[#9]

@GWHotmail 

How has it worked for you? I ended up being tied up (figuratively, of course) all weekend, so never got to look further. You seem the type who likes to figure things out for yourself......so I'm just checking in.