Dashboard Help- Widgets and COUNTIFS

megtro610

I am new to Smartsheet and making a dashboard. I want a widget to show as a pie chart illustrating how many :

1. "SRM"(child rows w/ purple check marked and bold text) there are [see screenshot below]

2. if it is "closed"(box checked) or not.

I know I need to make a separate metric sheet with a COUNTIFS formula referencing this sheet, but I don't know what to do next.

Thank you for your help

kowal

hi @megtro610,

If you want to create Chart widget from report it will take only row data from report and you cannot use numbers from grouping or counting etc.

For widget with that data put this information into Sheet Summary in sheet create reports out of Sheet summary and you will have this data ready for widgets.

Georgie

Hi @megtro610,

That’s right, you’ll need a separate metrics sheet to do this. I’d create a “Level” column in your main sheet first, to show the number of ancestors each row has - we can then use that column to count the number of “SRM” rows (since they are the first level children).

So, my suggestion is as follows:

1. Create a ‘Level’ column in your existing sheet to show the Level of each row, using =COUNT(ANCESTORS()).
2. Convert the formula to a column formula so that it applies to all rows - your sheet will then look something like this:
   
3. Then, on your separate metrics sheet, create formulas to count:
   1. Number of rows with the relevant level (in this case, level 1), using:
      1.  =COUNTIF({Main Sheet - Level}, 1)
      2. The cross-sheet reference (the section in curly brackets {}) is the entire “Level” column in main sheet - you’ll need to create the reference as you type the formula, and you can name the reference as desired
   2. Number of rows where Closed? Is 1, using:
      1. =COUNTIF({Main Sheet - Closed}, 1)
      2. Reference is the “Closed?” column in main sheet
4. You could also create metrics to count rows that aren’t closed and aren’t SRM rows, with:
   1. Number of not closed: =COUNTIFS({Main Sheet - Closed}, <>1, {Main Sheet - Level}, <>1)
   2. Number of rows that aren’t SRM: =COUNTIF({Main Sheet - Level}, <>1)
   3. Your metrics sheet would then look similar to below:
      
5. Once you’ve got your metrics sheet set up, you can then use that sheet as the source for charts on your dashboard and select the relevant rows. For example, you could select the bottom 2 rows  in the “Add Chart Data” window to show Closed vs Not Closed, as seen below:
   
6. Then in another chart, you could select the top 2 rows in the “Add Chart Data” window to show SRM vs not SRM, so that your pie charts would look something like this:
   
   

Check out the following articles for more information on the functions used in the formulas:

• Count
• Ancestors
• Countif
• Countifs

Does that work for you?

Georgie