Summarizing Data using CountIF

Ken Hankoff

I have created a Status Tracking sheet with multiple records.  Each record represents a summarized view into a detailed task template.  Each record also has a Status field, which we use to describe the phase of the project that we are in (there are four phases).  I have used Countif to count the number of records in a given Phase (10 in Discovery Phase, 12 in Engage Phase, 9 in Fix Phase, etc.).  The Countif formulas were created in rows under the records above, which contain the Phase information.  However, if someone sorts or filters the records in the Smartsheet, the rows with the formulas end up elsewhere (no longer on the bottom) in the sheet.

 

If this were Excel, I could create a formula in a separate sheet, that summarized the data on the target sheet, and it would self-adjust when the target sheet adjusted.  However, Smartsheet doesn't seem to allow me to create that kind of formula, and having the calculation on the main (target) sheet, ends up with my summary rows getting moved when someone sorts the sheet.

 

Any ideas on how to overcome this?  I'd use a Report, but can't put calculations into that either.  I could really use a creative solution.  Thanks.

Travis

Hi Ken, Try indenting all your rows under the row containing your formulas. This will ensure your formula row stays at the top of all your rows, even after you sort. Also, you can use the CHILDREN() function in your formula which will automatically include all child rows, even newly added rows (rather than selecting a range of cells). To indent your rows, highlight all the rows under the formula row and press the Indent button on your toolbar.