complex count formula needed

Friends, this is the essence of some data I have to work with. Col A contains the base location names. For various reasons there are also alternative names in cols C, D, E and F. Col B has the years - it is an 18 month rolling sheet so it currently is 2014 and 2015 (col B). Cols G through L have a list of drug classifications. As they come out of the original program they are drug b,drug d,drug a,drug c etc. Any order separated by commas. I use the text to columns to get then into their own cells. I consolidated them all in col Q.

Now the problem, I have to count them by the various locations (dropdown in col S) and different time frames (dropdown in col O). The final output for these variations is a graph (not included). I have struggled with various ways to get all 6 columns of drug data to count but to date I've only counted the first (col G). The only way I have figured out to do this is to use up to 60 columns beginning in col U and ending in col CB with concatenations to try to count the various permutations that the two dropdowns and the different drug classifications. I've tried two different count processes, one in col P and one in col T. Col P doesn't work if I use one of the alternate names or any of the date values (2014 or 2015). For col T the only way I think I can get it to work is to have up to 60 different countif formulas.

To complicate things I can't access the forum from work so I'll only be able to visit it using my iPad so any reuploading of the sheet I cannot see what solutions are proposed. I don't think VBA would be a solution since the end result is posted to a sharepoint site and has multiple users selecting what they want to see and I'm not versed in VBA. It would also be nice if I didn't have to use an array because the data varies every month.

If you write out the proposed solution and I can read it on my iPad I can test it at work.
I always give points if that is any help.

Is it an option to change your whole data layout so that it becomes a normalised 2 dimensional table of data. i.e where you have columns (or fields) for each type of data.

e.g columns for
Short Name
Date
Long Name Code, with values AK, AL, AM etc


If you are able to change to this layout then summarising/counting becomes a simple matter, either with a pivot table or a regular COUNTIFS() function.

Richard, not an option because there is more data in the actual sheet. Each location also has a single number and a unit and actual date, if I make some changes to more two dimensional I'll mess up other counts. I usually float between 4000 and 5000 rows and about 27 columns of actual data.

Joe, your suggestion intrigues me though I've never used that process before. Can you provide more details?

All, I'm heading to work shortly so I can only visit via the iPad and so my replies will be limited though when I get there I will immediately revisit this post.

Joe, as I noted I'm on an iPad and cannot see any formulas in attached sheets. Can you just explain it or copy/ paste any formulas?

I think you missed the point, if I select MDH BH and all periods or 2014, I need to know the count analgesics for that "location" either for all periods or for that year.

Originally Posted by Sambo kid

Richard, not an option because there is more data in the actual sheet. Each location also has a single number and a unit and actual date, if I make some changes to more two dimensional I'll mess up other counts. I usually float between 4000 and 5000 rows and about 27 columns of actual data.

I'd be extremely surprised if you couldn't create a normalised table and thus make analysis so much easier - even if you need a helper column or two. Your existing layout does not lend itself to analysis in anything like a straightforward way and you'll need to jump through all sorts of hoops if you continue to use that. Think about it like this. If your existing information is capable of analysis, and there's no reason to believe it can't, why enter it in such an un-ordered fashion and create analysis problems when the same information captured in a staightforward manner will overcome this.

Richard, the problem enters in like this... The info in col A is the "short name" for all the facilities. What isn't in this data set but is in the original is a column for units. Those can be categorized as behavioral health or rehab or regular. The people who want to see the compilation of the end data want to see all their location data, then they want to see BH only, then they want to see it w/o BH then they want to see rehab only, then w/o rehab and BH. And every row represents one event. So if I was understanding your suggestion it was to try to move the items like SJH BH or SJH2 to the same column as SJH. I'm not sure I can do it without doing it on a row by row basis but if I did move it down there are so many unseen columns I didn't put in my sample that I use for other counting.

The original data set was pretty easy because it just had the facilities, it was when people started asking for permutations that this thing had grown so ugly. I keep it in an access database so maybe I can do a different query that pulls it into one set of columns and put it in a separate spreadsheet and do my counting from that? I just was hoping that there was some formula I couldn't think of to use.

Hi,

Can you mock up a simple example (using your existing layout) and manually add a sample set of results for various permutations of your data. Explain which cells are results and in a narrative an explanation of how you have calculated the results. If we can see what you start with and what you wish to see as a result maybe this will become a lot clearer.

Everything in the attached sheet is representative of the data I have. The headings in row 2 for columns C through L are (AK, AL etc.) the actual columns in my original data. I put them there for reference. This is the only data I have to work with, original is a HUGE data set at work and for now I am not able to access excel forum from work so I sent this home. The items in columns C through F represent the alternate names for the units and they are created based on formulas I have in my original data. Basically the permutations of my data are in the columns to the right of column T.

So I'm trying to set up a formula to count the drug classifications for example SJH for 2014 for analgesics and analgesics could appear in any of the 6 columns listed as AC through AH (in this sheet they are actually columns G through L). I also have to be able to count all analgesics for SJH2 (which only appears in col D (AL)) for 2014 and for all periods (2014 and 2015). One problem is that the data comes out of the program like this: analgesic,anti-anxiety,anti-hypertensive,sleep aid. I use text to columns so I can get them into their own columns.

So I'm trying to populate either counts column (P or R) based on the dropdown selections made in cells O2 and S2.

Hopefully that helps.

Solved it! Took Richard's advice, created a new table in access that puts all the alt names in the same column and have pointed the countifs formulas to them and now I have the answers.