Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Reporting: Multi-Select Data, Best Practice?

I have a multi-select column I'm trying to count instances of for each quarter of each year. I created a make-shift report using a sheet and I'm looking for help to determine if there's a better way to do this.

Report I created:

I've duplicated that sheet into more columns so I could share the formulas I am using. The left side is the actual report I created (see first screenshot). It's saved as a sheet and not as a report because I can't figure out how to do this any other way.

This is what the report looks like now and I'm trying to determine if I've done it the best way (screenshot of above link):

Here's the sheet with the source data:

Notice that the "Business Owner Status", where we are doing our lookup on, is a multi-select column and often has multiple values in it.

Can anyone give me a better way to display this data IN THIS FORMAT using dashboard or a report?

Best Answer

  • ✭✭✭✭✭✭
    Answer ✓

    @NeilKY

    In my solution, instead of having a Year or Quoter value in the sheet cells, I put it in the Sheet Summary field or column formula. (For example, {Quarter}, CONTAINS("Q1", @cell) condition in the COUNTIF function.

    [Is Parent] =IF(COUNT(CHILDREN()) > 0, 1)
    [Business Order Status Col] =IF([Is Parent]@row, "", IF(Row@row < [Count Distinct Status]#, INDEX(DISTINCT({All Business Order Status Data}), Row@row)))
    [Q1] =IF([Is Parent]@row, SUM(CHILDREN()), IF(ISTEXT([Business Order Status Col]@row), COUNTIFS({BusinessOwnerStatus}, CONTAINS([Business Order Status Col]@row, @cell), {Year}, Year#, {Quarter}, CONTAINS("Q1", @cell))))
    [Q2] =IF([Is Parent]@row, SUM(CHILDREN()), IF(ISTEXT([Business Order Status Col]@row), COUNTIFS({BusinessOwnerStatus}, CONTAINS([Business Order Status Col]@row, @cell), {Year}, Year#, {Quarter}, CONTAINS("Q2", @cell))))
    [Q3] =IF([Is Parent]@row, SUM(CHILDREN()), IF(ISTEXT([Business Order Status Col]@row), COUNTIFS({BusinessOwnerStatus}, CONTAINS([Business Order Status Col]@row, @cell), {Year}, Year#, {Quarter}, CONTAINS("Q3", @cell))))
    [Q4] =IF([Is Parent]@row, SUM(CHILDREN()), IF(ISTEXT([Business Order Status Col]@row), COUNTIFS({BusinessOwnerStatus}, CONTAINS([Business Order Status Col]@row, @cell), {Year}, Year#, {Quarter}, CONTAINS("Q4", @cell))))

    Note: Year# is the value of the Sheet Summary field, 2020, for example.

    If you need a copy of the sheets, please contact me by email on my profile page.

Answers

  • ✭✭✭✭✭✭
    edited 12/16/24

    Hi @NeilKY

    The formula I came up with is as follows;

    [Q1] =IF([Is Parent]@row, SUM(CHILDREN()), IF(ISTEXT([Business Order Status Col]@row), COUNTIFS({BusinessOwnerStatus}, CONTAINS([Business Order Status Col]@row, @cell), {Year}, Year#, {Quarter}, CONTAINS("Q1", @cell))))

    [Q2] =IF([Is Parent]@row, SUM(CHILDREN()), IF(ISTEXT([Business Order Status Col]@row), COUNTIFS({BusinessOwnerStatus}, CONTAINS([Business Order Status Col]@row, @cell), {Year}, Year#, {Quarter}, CONTAINS("Q2", @cell))))

    I put the [Column Totals:] row as the Parent row to use the column formula.
    Thus, the IF([Is Parent]@row, SUM(CHILDREN()) formula is in front.

    Site faviconSmartsheet

    Somehow, your [Business Order Status Col] values in the Report: Business Order Status sheet have data incompatible with multi-select data in the ReportDataSource sheet.

    For example, in the 15th row, Asian-Owned Business has a trailing value (Chinese, Filipino, etc.) that is not in the multi-select value.

    I've found some un-Capitalized "Asian-Owned Businesses" entries in the image below.

    So, I retrieved the [Business Order Status Col] values from the original ReportDataSource sheet's Business Order Status values.

    Though creating the [Business Order Status Col] values or lists is not the central theme of this discussion, I have included the procedure for the community's reference.
    For detailed information on this procedure, please refer to my recent comment.

    All Business Order Status Data From Source

    This sheet gets all Business Order Status Data values from the Multiple-Select Value range of the ReportDataSource sheet.

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

    Then, the Report: Business Order Status 2 sheet gets the distinct values of the above All Businesses Order Status Data.

    =IF([Is Parent]@row, "", IF(Row@row < [Count Distinct Status]#, INDEX(DISTINCT({All Business Order Status Data}), Row@row)))

  • ✭✭✭
    edited 12/16/24

    🤯🤯🤯. Thank you for spending the time on that! Unfortunately, I am lost.

    Could you possibly list all the columns you added and to which sheet, and include the formulas of each?

    For the source data and the "Asian Owned" error, that was just a typo from when I was trying to share the data with the world. I had removed the extra text to make it easier but it just broke that value :) I've fixed it on the original sheet so hopefully that fixes it. However, do love that you created workaround, which I'd like to keep anyway just in case this happens again after I hand the sheet over to the user.

  • ✭✭✭✭✭✭
    Answer ✓

    @NeilKY

    In my solution, instead of having a Year or Quoter value in the sheet cells, I put it in the Sheet Summary field or column formula. (For example, {Quarter}, CONTAINS("Q1", @cell) condition in the COUNTIF function.

    [Is Parent] =IF(COUNT(CHILDREN()) > 0, 1)
    [Business Order Status Col] =IF([Is Parent]@row, "", IF(Row@row < [Count Distinct Status]#, INDEX(DISTINCT({All Business Order Status Data}), Row@row)))
    [Q1] =IF([Is Parent]@row, SUM(CHILDREN()), IF(ISTEXT([Business Order Status Col]@row), COUNTIFS({BusinessOwnerStatus}, CONTAINS([Business Order Status Col]@row, @cell), {Year}, Year#, {Quarter}, CONTAINS("Q1", @cell))))
    [Q2] =IF([Is Parent]@row, SUM(CHILDREN()), IF(ISTEXT([Business Order Status Col]@row), COUNTIFS({BusinessOwnerStatus}, CONTAINS([Business Order Status Col]@row, @cell), {Year}, Year#, {Quarter}, CONTAINS("Q2", @cell))))
    [Q3] =IF([Is Parent]@row, SUM(CHILDREN()), IF(ISTEXT([Business Order Status Col]@row), COUNTIFS({BusinessOwnerStatus}, CONTAINS([Business Order Status Col]@row, @cell), {Year}, Year#, {Quarter}, CONTAINS("Q3", @cell))))
    [Q4] =IF([Is Parent]@row, SUM(CHILDREN()), IF(ISTEXT([Business Order Status Col]@row), COUNTIFS({BusinessOwnerStatus}, CONTAINS([Business Order Status Col]@row, @cell), {Year}, Year#, {Quarter}, CONTAINS("Q4", @cell))))

    Note: Year# is the value of the Sheet Summary field, 2020, for example.

    If you need a copy of the sheets, please contact me by email on my profile page.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions