How to count Child Items based a dropdown list of criteria

I want to be able to summarize how many child rows are open in the Purchasing Status column. This is a dropdown list column. In this example I want to count what is Not Ordered and Ordered. Ideally I would like to do that in the Parent row for Detail "000", but as a dropdown column I suspect I'll need to enter the formula in another column, or perhaps even a linked sheet that pulls in data from the parent row for all jobs. I want to do this with multiple columns. I could use some direction on the best way to accomplish this and also the formula to count using that kind of criteria. I know how to do this in Excel, but struggling in Smartsheet.


  • AaronO
    AaronO ✭✭✭

    I think you can do just what you want. You can put formulas in dropdown columns as long as you have not restricted the column to dropdown items only. So in the "Purchasing Status" column at the 000 level you could put in a forumula like:

    =COUNTIF(children(),"Ordered") + " ordered"

    and see:

    You could do it a new column, too, of course. You could lock the 000 row so nobody can change the formula accidentally using the dropdown - if you select one of the options, the formula will be replaced.

    You'll also have to put the formula in every 000 row manually (though it'll be the same formula so copy/paste will work fine). If you put it in another column, you could make it a column formula (and wrap it in something that only produces a value if the row has children).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!