Multiple sorts/countifs of different columns
Hello - new user here!
I have a new sheet with ~200 rows with ~25 columns. I have created a form to feed data into the sheet and that is working great. A few of those columns include creator name, department, category, and a few other 'metadata-type' fields. I'm struggling to create summary reports of those 'metadata-type fields' as I know there will be future values/names that are not entered yet, but I know that I will want to be able to summarize on them.
Examples of summary reports would be number of entries by category, for a given creator or department:
Name Cat1 Cat2 Cat3 TTL
Alex 15 4 8 27
Dept1 70 55 45 170
I understand how to countif and match and contains, but can I automatically create a summary that grows for each discrete value in either name or department (I'm AOK is they are different reports)?? I would expect this report to grow to 10000s of rows and cannot be filtering and recreating reports all the time. The goal would be to have all this update on our executive dashboard.
Thanks - Ben
Best Answer
-
You would need to use a formula that pulls one instance of each name into a delimited string. Then you can parse this list down a column or across a row.
HERE is a link to a sheet that provides a parsing solution from another sheet down a column. It requires two "helper" columns and you would need to "pre-fill" the formula into as many rows as you anticipate needing.
Answers
-
In my opinion, the best practice is to use an external sheet to generate global metrics for your source sheet.
This is oftentimes sufficient enough to get said metrics/KPIs onto a dashboard in the form of charts and metric widgets.
You can also build reports off of the metrics sheet to generate reports if you want to see that data specifically.
So this could look something like this (note I added asterisks in front of formulas so the text is visible)
If you have multiple names and want to see the number of entries for a specific name, something like this would work
If you repeat this structure for each category, you can classify the categories to easily generate reports using an additional column that displays the category name. In the report builder, to get a report for Name, you would reference this sheet and in the WHAT have it include rows where Report is equal to name. If you configure the Report column as a dropdown it will make it easier to do this.
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
Thank you Dan P.
This makes sense if I knew the number of "names" - currently 25, but will grow. I need a dynamic list of those names first, then your formulas could be used to count the entries by category by name.
Thoughts?
-
Hi @Ben Scholz
To add to Dan’s excellent advice.
I agree that a so-called Master Metric sheet would work best.
It can be structured to be dynamic, so it can grow with what you need.
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks again.........
Building the dynamic list is the current hurdle I can get across. Yes, in a separate sheet, I'm calculating my metrics in a table, using =CountIFS to intersect the row & column to count the number of instances...........
=COUNTIFS({Risk.Register2 Inherent Risk}, CONTAINS([Column2]$2, @cell), {Risk.Register2 RiskOwner}, $[Primary Column]3)
How do I build the dynamic list so that as new names are added to the original sheet (or DB) my separate metrics sheet automatically adds the new name and applies the formulas?
Ben
-
You would need to use a formula that pulls one instance of each name into a delimited string. Then you can parse this list down a column or across a row.
HERE is a link to a sheet that provides a parsing solution from another sheet down a column. It requires two "helper" columns and you would need to "pre-fill" the formula into as many rows as you anticipate needing.
-
Happy to help!
I saw that Paul answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives