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

I’m working on building a Smartsheet dashboard and need some help setting up my data for visualization. I have two specific questions:
- 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? - 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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!