Formula to list all projects that asked for additional funding and count number of times

Edward Spencer

All, I want to do the following, if it is possible, for metrics that I need to include on my Smartsheet dashboard. I have tried variations of =COUNT(DISTINCT(COLLECT without success.

Here is what I want to do:

In a metrics sheet, I want to go to my "2023 IAP Tracker ELT Approvals" sheet, then automatically list all of the project names found in the "Project Name" column then count the number of times additional funding was asked for in the column "Current Request: First or Additional Funding?" = Additional Funding -- I want a count for Additional Funding.

So for example, the metric I ultimately want to see is:

Project Name.............Count of Additional Funding Request

XYZ Project ................................. 4

ABC Project...................................3

If a formula to do this is not possible, any other ideas on a good way to report this metric on a dashboard?

Thank you!

-Edward

Scott Orsey

Hi @Edward Spencer, You're close... you just have a parentheses in the wrong spot. See bolded...

= INDEX(DISTINCT({2023 IAP Tracker ELT Approvals Range 5}), Index@row)

Good luck

Paul Newcome

If you are using metrics widgets, you are going to need to still use (for example) 5 metrics widgets. Based on the above screenshot and removing those with zero as a result would mean that two of those metrics widgets will show as blank space on your dashboard. Either way you are taking up dashboard real-estate.

I wasn't suggesting using a report widget. I was suggesting to use the report to create a dynamic chart (which would also exclude zero counts).

Paul Newcome

I would suggest creating a row report fed by the source sheet. You can then group by Project Name and use the report summary feature to get your counts. Once the report is built, you can use it to create a chart widget on your dashboard such as a bar or column chart.

Scott Orsey

Hi @Edward Spencer , What you want is very doable. It is possible to do it the way you are trying, but I also suggest trying to get a report to work which would update more immediately than an intermediate sheet like you are trying. I'll share some thoughts for the report as well as the approach you are taking:

1) Report approach: If you create a report grouped by Project Name, you would then filter by "additional funding" in the Current Request column. Then... use Summarize to count any value on the report. What you should see is collapsible sections for each Project Name with only the records that have additional funding. In the header row of each grouping you will have the count of the number of requests that meet that criteria. Then, you can use this grouped/summarized report in a chart widget on your dashboard. It will show the summarized counts on the chart.

2) Summary sheet approach: On your metrics sheet, create the following columns: Index, [Project Name] and Count. In the Index column simply list the numbers 1,2,3,... up to the absolute maximum number of Project Names you expect. Go over a bit to be sure. Then in the Project Name column put the following formula:

= INDEX(DISTINCT({Project Name column from other sheet}), index@row)

Copy this down the column (or use a column formula). This will then find all of the distinct project names and list them one at a time in this sheet

In the Count column, us the following formula:

= COUNTIFS({Project Name column from other sheet},[Project Name]@row, {Current Request column from other sheet},"Additional Funding")

Copy this down the column (or use a column formula). This will count all of the rows from the other sheet for a given project name with Additional Funding.

Then you can use this sheet for your chart widget and dashboard.

Good luck and be well!

Edward Spencer

@Scott Orsey So I followed your instructions but I am having trouble with = INDEX(DISTINCT({Project Name column from other sheet}), index@row) it is returning #INCORRECT ARGUMENT.

I really want to use a metric vs. having a report on the dashboard due to limited space on the dashboard because of all the other information the stakeholder wants to see.

Scott Orsey

Hi @Edward Spencer, You're close... you just have a parentheses in the wrong spot. See bolded...

= INDEX(DISTINCT({2023 IAP Tracker ELT Approvals Range 5}), Index@row)

Good luck

Edward Spencer

@Scott Orsey, thank you!! I got it to work. I do have one follow up question. Is there anything I can add to not return include Project names that have a count of 0? See below....

Thank you,

-Edward

Paul Newcome

If you are using metrics widgets, you are going to need to still use (for example) 5 metrics widgets. Based on the above screenshot and removing those with zero as a result would mean that two of those metrics widgets will show as blank space on your dashboard. Either way you are taking up dashboard real-estate.

I wasn't suggesting using a report widget. I was suggesting to use the report to create a dynamic chart (which would also exclude zero counts).

Scott Orsey

Hi @Edward Spencer, I like @Paul Newcome's suggestion of using the report approach with a dynamic chart, though I'm not certain that would exclude the zeros... I think the report would still calculate 0 for the summary row for a given project and chart it (though Paul can correct me if I'm wrong... he is a wizard!). However, if you created a row report off your metrics sheet, you could then filter the zero rows.

If instead, you prefer to pursue the metrics sheet (which is becoming a puzzle to solve!), then what you are trying to do is circular... you only know that you want to exclude the project AFTER you have done the count. You will need something on your main sheet that tells you that there are no projects that meet the criteria (this would also solve the dynamic chart problem I mentioned above). The way I solved a similar problem in one of my tools was to add a helper column to the main sheet where you do the COUNTIFS() calculation. So every row for a given project would have the same number... the count of rows that meet the criteria for your metrics sheet. Then you would change the formula in the Project Name column on your metrics sheet to be something like:

=INDEX(DISTINCT(COLLECT({2023 IAP Tracker ELT Approvals Range 5},{2023 IAP Tracker ELT Approvals Count column},>0)),Index@row)

and since you already know the count because it is on the other sheet, you could simplify your Count column to be:

INDEX({2023 IAP Tracker ELT Approvals Count column},MATCH({2023 IAP Tracker ELT Approvals Range 5},[Project Name]@row,0))

But... I still think it might be worthwhile to try the report approach. I hope this is making sense :)

Good luck!