Multiple sorts/countifs of different columns

Ben Scholz

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

Paul Newcome

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.

SoS | Dan Palenchar

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.

Ben Scholz

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?

Andrée Starå

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!

Ben Scholz

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

Paul Newcome

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.

Andrée Starå

@Ben Scholz

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée