How to count total number of requests submitted?

Options

Hi, I am trying to find a formula to count the total number of requests submitted in my grid. I want the formula to count the number of request ID's displayed. I want to use this number so I can display a metric on the dashboard. I also want to use a formula to count the number of "In Progress" requests and use this to display as a metric on the dashboard as well. So I need 2 formulas, one for total number of requests submitted and one for total number of "In progress" requests.

Answers

  • brianschmidt
    brianschmidt ✭✭✭✭✭✭
    Options

    I would recommend using a COUNT(COLLECT( formula.

    For your total number of requests, it would look something like this:

    =COUNT(COLLECT([Request ID]:[Request ID], [Request ID]:[Request ID], NOT(ISBLANK(@cell))))

    For total number of requests in progress, it will likely look something like this:

    =COUNT(COLLECT([Request ID]:[Request ID], [Request Status]:[Request Status], "In Progress"))

    The sample fields in brackets [ ] are your column ranges. The first formula essentially states, count the total number of request ids that you can collect where the request id field is not blank. The second formula state to count the total number of request ids where the request status column equals "In Progress"

    Hope this helps!:)

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @Kelsee Katsanes

    As I don’t know column names I can only make assumptions.

    =COUNT(Column:Column)

    =COUNTIF(Column:Column,”In Progress”)

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • brianschmidt
    brianschmidt ✭✭✭✭✭✭
    Options

    @Kelsee Katsanes

    @Mark.poole is totally right on this one. Disregard my comment. The collect function is very useful with other applications, but doesn't work with count given the count and countif(s) formulas Smartsheet has.

    =COUNT([Request ID]:[Request ID])

    =COUNTIF([Request Status]:[Request Status],”In Progress”)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!