How to roll up based on column selection and multi-column answers in Smartsheet (Community)

first

Learn how to roll up based on column selection and number of responses across multiple columns in Smartsheet. Specifically, as shown in the first table below, in the source data, business units are selected in the row direction, and there are multiple apps that you want to evaluate in the vertical direction, and each cell in this vertical and horizontal table has an important value. , not important, somewhat important, etc. Based on this response data, as shown in the second table, based on the business unit and evaluation, for example, how many business units answered as not important? It does a rather complex rollup of whether there is (suming the answers for all Apps).

Question summary

KPeltzel created a form to get feedback from six different business units on 48 applications. Participants select their business unit from the drop-down list and rank each application based on importance.

image.png

KPeltzel wants to summarize how often each business unit selects something based on importance.

image.png

Answer summary

This discussion included several stages of question and answer exchange.

Answer assuming that there are no multiple business units for aggregation criteria in the original data.

In the first answer in the answer data, in the first table aboveBusiness Unitcolumn is Business Unit 1, Business Unit 2, etc.Assuming that you only have to make one selection at a timeproposed the following formula.
=COUNTIF(INDEX([App 1]:[App 5], MATCH(Business#, Business:Business)), [selected answer]#)
The key to this formula is to use the INDEX function to retrieve rows that match the business unit selected by the business unit from columns App1 to App5. Normally, the INDEX expression selects one column and combines it with MATCH to get only the value 1 in a specific row, but we proposed an idea to get the range of selected rows and pass it to COUNTIF. doing.

Answer based on the premise that multiple business units for aggregation criteria appear in the original data

However, in subsequent interactions, the respondent corresponding to Name in the first table above was found to be unique.I found out that there will be multiple business units.. In other words, it is assumed that person A will answer about business unit 1, and person C will also answer about business unit 1. Then, the above formula will not solve the problem, soAdd helper columnThen, in that column, count how many answers there are for each answer, e.g. Not Important, and use that count to count how many there are for the selected business unit, e.g. business unit 1.2 step methodI decided to take it.

How to quote in formulas when column names contain spaces or numbers

In addition, the questioner can use “:” to connect words with spaces between them, such as “Business Unit” in MATCH(“BU 2”, Business Unit:Business Unit), to indicate a range. I was trying to specifyIf the column name contains spaces or numbers, enclose it in square brackets., i.e., [Business Unit]:[Business Unit].

solution

  1. Create a helper column
    First, create a helper column that will add the answers for each row. Use the following formula for this column:
    [# of Answers]
    =COUNTIF([System 1]@row:[System 49]@row, Answer#)
    To explain the formula, for systems 1 to 49 in that row, those whose answers are selected (the value of the summary field of Answer #, for example, Not Important) are counted.
  2. Use the COUNTIFS function
    Then use the COUNTIFS function to sum the values ​​in the helper columns that match the selected business units.
    =SUMIFS([# of Answers]:[# of Answers], [Business Unit]:[Business Unit], Business#)

Summary


You can combine COUNTIF functions, helper columns, SUMIF functions, etc. to roll up based on selections from one column and number of answers across multiple columns. This method is useful for collecting feedback from multiple business units and for analyzing responses based on importance.

Key Points

  • If you have multiple values ​​for the same business unit column, you must use a helper column to count the responses in each row.
  • The helper column uses the SUM function to count the number based on the selected answers.
  • If the column name contains spaces or numbers, enclose it in square brackets.



Translation of correspondence

K Pelzel ✭✭
01/18/24

We created a form to get feedback from 48 different business units on 6 different applications. Participants select their business unit name from a single selection dropdown. We then ranked each application as ``I don't use it myself or on my team,'' ``I'm not sure,'' ``Not important,'' ``Somewhat important,'' ``Somewhat important,'' ``Quite important,'' and ``Very important.'' Rank them in order. Drop down again and he selects one.

The sheet that the form is fed into looks like this:

image.png

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

Currently, we aggregate the "Importance" responses by application (48 columns), but we want a higher level rollup based on business unit. My table looks like this.

image.png

I've tried several variations of the formula, but they either don't parse or return the wrong set of arguments. The latest thing I've tried is the following, but I can't parse the error:

=COUNTIFS({BusinessUnit}, “BusinessUnit 1”, {AllSystemColumns}, HAS(@row, “Very Important”))

{AllSystemColumns} is the name range of 48 columns to check (in the example above, it would be App 1, App 2, App 3, App 4, App 5).

I've tried @cell or @row (to search the entire row), but neither works. We would appreciate your cooperation.


Exchange of questions and answers

  • jmyzk_cloudsmart_jp ✭✭✭✭✭✭
  • 01/19/24 Edit 01/19/24
    • Hello@KPeltzel
      First, I tried using COLLECT to filter the range. (In my case, App1:App5.)
      However, since I need to filter a 2-dimensional range horizontally like a row, INDEX(MATCH()) gave me the desired result.
      =COUNTIF(INDEX([App 1]:[App 5], MATCH(Business#, Business:Business)), [Selected Answer]#)
      Example: =COUNTIF(INDEX([App 1]:[App 5], MATCH(“Business Unit 4”, Business:Business)), “Not Important”)
      In my demo, instead of a separate sheet with a business and answer combination table, I use two fields in the sheet summary to display the number I want. However, the structure of the expressions must be the same. (Visit the link below and change the business and answers to see the demo sheet in action.
    • image.png
    • (I created conditional formatting only for "Not Important", so select "Not Important" to see the same image)
  • K Pelzel ✭✭
  • 01/19/24 Edited on December 2024, 1
    • @jmyzk_cloudsmart_jp
      This partially worked.
      I have a metrics sheet, but I started with an overview of the sheet to see if this would work. You can get the overall count with =COUNTIF([System 1]:[System 3], “Not Important”).
      Entering INDEX and MATCH results in "unparsable". =COUNTIF(INDEX([System 1]:[System 3], MATCH(“BU 2”, Business Unit:Business Unit)), “Not Important”)
  • jmyzk_cloudsmart_jp ✭✭✭✭✭✭
  • 2024/01/21 Edited on 2024/01/21
    • Hello @KPeltzel
      If the column name contains spaces or numbers, enclose them in square brackets.
      =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, you should use COUNT for the Number of Surveys field.
      =SUM([Row ID]:[Row ID]) vs =COUNT([Row ID]:[Row ID])
  • K Pelzel ✭✭
  • 2024/01/22 Edited on December 2024, 1
    • @jmyzk_cloudsmart_jp It's so close! First of all, thank you for the explanation about parentheses and column names. it was very helpful. Also, it's COUNT instead of SUM.
      The formula is working, but not all inputs/columns are counted. I'm counting across 47 columns, is there a limit to how many I can count? It only returns 35, but if I count all occurrences of "not important" I get 304, so I'm missing something. Any opinions? It works when I work with a draft table that spans 3 columns, but I seem to run into problems when I try to sum more columns.
      All 47 columns appear to be highlighted and indexed on the table, but the totals returned are incorrect.
      =COUNTIFS(INDEX([System 1]:[System 47], MATCH(“BU 1”, [Business Unit]:[Business Unit])), “Not Important”)
  • jmyzk_cloudsmart_jp ✭✭✭✭✭✭
  • 01/23/24
    • Hello@KPeltzel
      Suppose you have multiple identical business units in the business unit column, such as "BU 1". In this case, the formula will not work because the MATCH() function returns the row number where the specified business unit first appears.
      If you need to count all "unimportant" answers for the "BU 1" selection, add a helper column that adds the answers for each row.
    • image.png
    • Then sum the values ​​in the helper columns that match the selected business units.
      =SUMIFS([number of answers]:[number of answers], [business unit]:[business unit], business#)
    • image.png
    • https://app.smartsheet.com/b/publish?EQBCT=eb5bcc7c9d2d4c508d75d5a419ea0a93
  • K Pelzel ✭✭
  • 24/01/24
    • @jmyzk_cloudsmart_jp That's what I was missing! Multiple business units answered these questions. It took me a minute, but I finally got an overview of the sheet and was able to recreate the formula for the metric sheet. Thank you very much for your help!
  • jmyzk_cloudsmart_jp ✭✭✭✭✭✭
  • 24/01/24
    • @KPeltzel I'm glad to hear that you solved your problem with the sheet overview and recreated the formula. 😁

Leave a comment