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
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]")
Comments
-
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.
-
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?
-
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.
-
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
-
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.
-
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.
-
Jim,
Good idea. I will try that.
Thanks.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives