Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Formula to count project status

Jeff Smith
Jeff Smith ✭
edited 12/09/19 in Archived 2016 Posts

I have a formula that is counting the different status of projects. This is what I have so far but the counts are not accurate. This might be because there are children however the total count is correct. 

Any ideas to what is missing? 

 

 

="Status Count:     " + COUNT(Status:Status) + "                                     In Progress.................. " + COUNTIF(Status:Status, "[In Progress]") + "      Not Started......................" + COUNTIF(Status:Status, "[Not Started]") + "      Complete.................. " + COUNTIF(Status:Status, "Complete") + "      Live Phase 1......................" + COUNTIF(Status:Status, "[Live Phase 1]") + "      At Risk .................. " + COUNTIF(Status:Status, "[At Risk]") + "      Live Phase 2......................" + COUNTIF(Status:Status, "[Live Phase 2]")

· Share on FacebookShare on Twitter

Comments

  • Jim Hook
    Jim Hook ✭✭✭✭✭✭

    While I don't completely understand your formula or intent, one thing looks odd to me. The use of square brackets ( [ ] ) is unusual unless the status text also includes them. I would expect to see "Not Started" as an argument rather than "[Not Started]". I'm also not sure what you mean when you say that the counts are not accurate but because there are children the count is accurate.

    · Share on FacebookShare on Twitter
  • JohnHinkle
    JohnHinkle ✭✭✭✭✭✭

    Could you provide more info to help us troubleshoot? Maybe a screenshot showing how the counts are incorrect, what you expect the count to be, what it is showing up as, etc?

    · Share on FacebookShare on Twitter
  • Jeff Smith
    Jeff Smith ✭

    I've used the square brackets where a column header is more than one word like Not Started. 

    Here is a screen shot of what I'm seeing. I have a summary of the different status' but they aren't correct. For example you can see in the screenshot there are 9 tasks that are In Progress yet my formula shows 0. I hope this helps. 

     

    Status Screenshot.jpg

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    As Jim mentioned, something looks odd

     

    This

     "[Live Phase 2]"

     

    is a text that results in 

     

    [Live Phase 2]

     

    This

     

    [Live Phase]2

     

    is a reference to column Live Phase, row 2

     

    Lots of your references or values are not using the correct syntax.

     

    In the COUNTIF functions, the second argument is a value.

    Again, "This2" is a text value of This2. This2 or [This]2 is a reference to the value located in column This and row 2.

     

    Craig

    · Share on FacebookShare on Twitter
  • Jeff Smith
    Jeff Smith ✭

    Thanks everyone. I thought I needed the square brackets when there was more than 1 word in the column header or cell value. I think that fixed it. 

    · Share on FacebookShare on Twitter
  • Jim Hook
    Jim Hook ✭✭✭✭✭✭

    Jeff,

     

    Instead of trying to make one large formula that converts all the counts into one long text string, why don't you have a separate row for each type of status? Use the first column for the status type and then use the COUNTIF() function in the second column to count the number of instances in the data range of each status type. For example if the first row in the first column has "In Progress" in it, to count the number of instances of In Progress put the following formula in column 2 of the first row:

     

    =COUNTIF(criteria_range, R1C1) where R1C1 is the cell reference with "in Progress" in it and criteria_range is the range of cells you want to scan.

     

    Then all you have to do is enter all the different status types in column1 and copy the same formula down next to them in column 2.

    · Share on FacebookShare on Twitter
  • Jeff Smith
    Jeff Smith ✭

    Jim,

    Good idea. I will try that. 

    Thanks. 

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories