Roll up based on selection from one column and count of answers across mulitple columns

KPeltzel
KPeltzel ✭✭
in Formulas and Functions

I created a form to get feedback from 6 different business units regarding 48 different applications. The participants select their business unit name from a single select dropdown. They then rank each application as “Not Used By Myself or My Team”, “I'm Not Sure”, “Not Important”, “A Little Important”, “Somewhat Important”, “Quite Important”, “Very Important”, again single select dropdown. 

The sheet the form feeds into looks like this:

image.png

I would like to summarize how often each business unit selects something based on importance. 

I am currently summing up the “importance“ answers by application (48 columns) but I need a high-level roll-up based on business units. With my table looking like this.


image.png

I’ve tried several variations of formulas but they are either coming back unparsable or incorrect argument set. The latest one I tried is the following and the error is unparsable. 

=COUNTIFS({BusinessUnit}, "Business Unit 1", {AllSystemColumns}, HAS(@row, "Very Important"))

The {AllSystemColumns} is a name range of the 48 columns to check (Example from above would be App 1, App 2, App 3, App 4, App 5). 

I’ve tried @cell or @row (since it’s looking across the rows) but neither works. 

Any help would be greatly appreciated. 

Tags:
· Share on FacebookShare on Twitter

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 01/19/24

    Hi @KPeltzel

    At first, I tried to use COLLECT to filter the range. (App1:App5, in my case.)

    But, since we want to filter the two-dimensional ranges horizontally, like a row, the INDEX(MATCH()) produced the desired result.

    =COUNTIF(INDEX([App 1]:[App 5], MATCH(Business#, Business:Business)), [Chosen Answer]#)

    Example: =COUNTIF(INDEX([App 1]:[App 5], MATCH("Business Unit 4", Business:Business)), "Not Important")

    My demo uses the Sheet Summary two fields to show a desired count instead of a separate sheet with a Business and Answer combination table. But, the formula structure should be the same. (Access the link below and change the Business and Answers to check how the demo sheet works.

    https://app.smartsheet.com/b/publish?EQBCT=eb5bcc7c9d2d4c508d75d5a419ea0a93
    image.png

    (I have created conditional formatting only for "Not Important", so to see the same image, choose "Not Important" )

    · Share on FacebookShare on Twitter
  • KPeltzel
    KPeltzel ✭✭
    edited 01/22/24

    @jmyzk_cloudsmart_jp

    This partially worked for me.

    I have a metrics sheet but I started with a sheet summary to see if I can get this to work. I am able to get the overall count with =COUNTIF([System 1]:[System 3], "Not Important")

    Once I put in the INDEX and MATCH I get "Unparseable". =COUNTIF(INDEX([System 1]:[System 3], MATCH("BU 2", Business Unit:Business Unit)), "Not Important")

    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 01/21/24

    Hi @KPeltzel

    If you have spaces or numbers in a column name, enclose them in a square bracket.

    =COUNTIF(INDEX([System 1]:[System 3], MATCH("BU 2", Business Unit:Business Unit)), "Not Important")

    =COUNTIF(INDEX([System 1]:[System 3], MATCH("BU 2", [Business Unit]:[Business Unit])), "Not Important")

    By the way, the Number of Surveys field needs to use COUNT.

    =SUM([Row ID]:[Row ID]) vs =COUNT([Row ID]:[Row ID])

    · Share on FacebookShare on Twitter
  • KPeltzel
    KPeltzel ✭✭
    edited 01/22/24

    @jmyzk_cloudsmart_jp I'm so close! First, thank you for the explanation regarding the brackets and the column names. That was very helpful. Also the COUNT rather than SUM.

    The formula is working but it's not counting all of the inputs/columns. I am counting across 47 columns, is there a limit to how much this can count? It's only returning 35 but if I count all occurrences of "not important" it's 304 so I'm missing something. Any thoughts? When I work in my draft table across 3 columns, it works but it seems to have issues once I try to sum more columns.

    All 47 columns are highlighted on the table it looks like it's indexing them but the total coming back is incorrect.

    =COUNTIFS(INDEX([System 1]:[System 47], MATCH("BU 1", [Business Unit]:[Business Unit])), "Not Important")

    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @KPeltzel

    Suppose you have multiple same business units like "BU 1" in the business unit column. In that case, the formula won't work, as the MATCH() function returns the row number of the first occurrence of the given business unit.

    If you need to count all the "Not Important" responses of "BU 1" selections, I would add a helper column to add the responses for each row.

    image.png

    Then, Sum the helper column value that matches the chosen business unit.

    =SUMIFS([# of Answers]:[# of Answers], [Business Unit]:[Business Unit], Business#)

    image.png

    https://app.smartsheet.com/b/publish?EQBCT=eb5bcc7c9d2d4c508d75d5a419ea0a93

    · Share on FacebookShare on Twitter
  • KPeltzel
    KPeltzel ✭✭

    @jmyzk_cloudsmart_jp that's what I was missing! I did have multiple business units answering these questions. It took me a minute but I finally figured it out with the sheet summary and I was able to recreate it the formulas in my metric sheet. Thank you so much for your help!

    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    @KPeltzel Glad to hear you resolved it with the sheet summary and recreated the formulas. Happy to help😁

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!