Google Sheets Formula Translation

tbittick

Greetings all - got one of those rush requests from on high to create a form for managers across our enterprise to vote on their top four future projects.

All of that went fine.

However, because I'm "formula-challenged" gather the results as been a little challenging.

The Mrs. Imported the sheet into her native language (Google) and had this done in minutes.

Now, if somebody would be so kind as to decipher into Sheet language I would be obliged.

The data collection sheet has four columns named Goal 1, Goal 2, Goal 3 and Goal 4 and each one has 55 options for them to choose from.

I need to be able to show in descending order which project got the most and least order etc.

In the formula the top Project in each Goal is shown, and then a second top ten list for each.

Please let me know if I can or need to provide more information.

={"Goal 1","Count";ARRAY_CONSTRAIN(SORT({UNIQUE(FILTER('GLWA Project Ranking'!K2:K13,'GLWA Project Ranking'!K2:K13<>"")),ARRAYFORMULA(COUNTIF('GLWA Project Ranking'!K2:K13,SUBSTITUTE(SUBSTITUTE(UNIQUE(FILTER('GLWA Project Ranking'!K2:K13,'GLWA Project Ranking'!K2:K13<>"")),"*","~*"),"?","~?")))},2,FALSE,1,TRUE),1,2)}

Erin Greunke

I'm not sure if this will work for your project or not, but in addition to your intake sheet, have you considered creating a metric rollup sheet and then using the RANKEQ function (https://help.smartsheet.com/function/rankeq)? Then you could create a report from there to display on a dashboard or just share the report.

So, as an example, for an exit interview survey, you could have a questionnaire for departing employees to complete in which some of the questions are multi-select, dropdown lists. As responses come in the rank actively updates on your metric roll-up sheet and report. Here's a couple screenshots of how I've set the metric sheet up for the project I was working on that ranked incoming answers to a question:

List all available options under the question (or in your case, Goal 1, 2, 3, etc). Then create a Count column that cross-references to your intake sheet:

Create a Rank column that will let you know what "place" the response comes in based on the Count column

Create a report that only shows Ranks for 1 - 5 (or whatever parameters you choose in the report builder)

tbittick

Erin thank you so much for your help.

A couple of follow up questions.

I set up the roll up sheet (or at least a start)

First to clarify, I have four goals and each one has the same 55 options. So if I understand the above, I need to create the Goal 1, Goal 2 etc and each one has to contain the 55 options as child rows?

Then I'm just not sure where to put the CountIf or Rank statements or quite frankly what they should be.

I'm mostly asking for help, instead of doing more legwork on my part, is because the data is already rolling in and I need to be able to provide some results. Would rather do that in Smartsheet than Google sheets. lol

Thoughts?

Erin Greunke

@tbittick

Based on my understanding of what you're trying to do, I built a simplified version quickly.

Your intake sheet would look something like this:

Your rollup sheet would look something like this:

The formula that goes in the count column under each goal would be =COUNTIFS({[YOUR CROSS-SHEET REFERENCE NAME]}, CONTAINS([Primary Column]@row, @cell))

Then, in each Goals' section in the Rank column, you would use the RANKEQ function -- Mine looks like this =RANKEQ(Count8, Count8:Count12, 0) as pictured below.

Hope this helps point you in the right direction!

Erin