Formula to count project status

Jeff Smith

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]")

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.

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?

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. 

 

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

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. 

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.

Jeff Smith

Jim,

Good idea. I will try that. 

Thanks.