Combining Totals Across Columns and Filtering Data for Chart Widget in Smartsheet

Renee Dillard
Renee Dillard ✭✭✭
edited 01/27/25 in Formulas and Functions

I’m working on building a Smartsheet dashboard and need some help setting up my data for visualization. I have two specific questions:

  1. Combining Totals Across Columns for Chart Widget:
    I have multiple columns named Status of Inquiry 1, 2, and 3. Each column has the same dropdown options: Closed, In Progress, Forwarded. For each staff member, I’d like to calculate the total count of each status (e.g., total "Closed," total "In Progress") across all these columns combined—not as separate occurrences. I then want to display these combined totals in a chart widget. Is there a way to create a formula that consolidates these values across columns for use in a widget?
  2. Filtering Based on Staff Names Across Two Columns:
    I have two columns: Staff Name (autofilled) and Manual Entry (for when the item is reassigned but still logged under the original staff). I’d like to create a chart or report where if the same name appears in both columns, all inquiries associated with that name are grouped together and displayed on the widget. Is there a formula or approach to achieve this kind of filtering and grouping?

if this helps, I'd like to use this formula in a metrics sheet vs. a report if possible.

Thanks in advance for any insights or suggestions!

Answers

  • Melissa Yamada
    Melissa Yamada ✭✭✭✭✭

    Hello @Renee Dillard

    For #1, you can create something like this on your metric sheet:

    Then on the count column, try this formula on each corresponding cells for Closed, In Progress, and Forwarded:

    =COUNTIFS({Status of Inquiry 1},Metrics@row) + COUNTIFS({Status of Inquiry 2},Metrics@row) + COUNTIFS({Status of Inquiry 3},Metrics@row)

    For #2, you can create a helper column on your source sheet, you can use a checkbox column and add the formula:

    =IF([Staff Name]@row=[Manual Entry]@row,1,0)

    Then create a row report and add a filter where helper column is checked, then group by either Staff Name or Manual Entry.

    Melissa Yamada
    melissa@insightfulsheets.com
    Data made simple, spreadsheets reimagined

  • I tried the first formula, and it did not work.

    I used the AI tool in Smartsheet to make this formula from the source sheet and it did what i needed it to, BUT this formula needs to go onto the metric sheet and it is not translating the same.

    =COUNTIFS({PA Status}:{Pa Status}, "closed", {Staff Name}:{Staff Name}, "NAME") + COUNTIFS({PA Status 2}:{PA Status 2}, "closed", {Staff Name}:{Staff Name}, "NAME") + COUNTIFS({PA Status 3}:{PA Status 3}, "closed", {Staff Name}:{Staff Name}, "NAME") + COUNTIFS({PA Status}:{PA Status}, "closed", {Manual Name}:{Manual Name}, "NAME") + COUNTIFS({PA Status 2}:{PA Status 2}, "closed", {Manual Name}:{Manual Name}, "NAME") + COUNTIFS({PA Status 3}:{PA Status 3}, "closed", [Manual Name}:{PA Manual Name}, "NAME")

    I already went in and changed the reference sheet names so they match but i am getting a UNPARSEABLE message

  • I have my metric sheet set up similarly to capture the total Closed, In progress, forwarded, but i need to know these for each staff member as well

  • Melissa Yamada
    Melissa Yamada ✭✭✭✭✭

    Hello @Renee Dillard

    You can create a metric sheet like this:

    Try this formula:

    =COUNTIFS({PA Status}, Closed$1, {Staff Name}, $[Metrics 1]@row) + COUNTIFS({PA Status 2}, Closed$1, {Staff Name}, $[Metrics 1]@row) + COUNTIFS({PA Status 3}, Closed$1, {Staff Name}, $[Metrics 1]@row) + COUNTIFS({PA Status}, Closed$1, {Manual Name}, $[Metrics 2]@row) + COUNTIFS({PA Status 2}, Closed$1, {Manual Name}, $[Metrics 2]@row) + COUNTIFS({PA Status 3}, Closed$1, {Manual Name}, $[Metrics 2]@row)

    Then you can just drag the formula and it will update the criterion like this:

    With this approach you can create a more scalable solution in a way that you won't need to manually update the "Name" in the formula.

    Melissa Yamada
    melissa@insightfulsheets.com
    Data made simple, spreadsheets reimagined

  • thank you for the assistance. I couldn't get a formula to work on my metric sheet so i ended up having to make helper columns in my source sheet, also using AI to make a formula similar the above and doing a cell merge to my metrics sheet. there are only 8 staff members I had to do this for so the lift wasnt too bad. This will have to work for now.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!