How to count total number of requests submitted?

Kelsee Katsanes
Kelsee Katsanes ✭✭
in Formulas and Functions

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.

Tags:
· Share on FacebookShare on Twitter

Answers

  • brianschmidt
    brianschmidt ✭✭✭✭✭✭

    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!:)

    · Share on FacebookShare on Twitter
  • Mark.poole
    Mark.poole ✭✭✭✭✭✭

    @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.

    · Share on FacebookShare on Twitter
  • brianschmidt
    brianschmidt ✭✭✭✭✭✭

    @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”)

    · Share on FacebookShare on Twitter
  • ClanMcDe
    ClanMcDe ✭✭
    https://community.smartsheet.com/discussion/comment/441155#Comment_441155

    @mark.poole, I have a similar issue, I need to add a count to a metric sheet that looks at the Task Schedule and counts tasks that are not Adhoc, or upon request.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!