Changing Status to a number in order to chart

nbannwart10

Hello! I am needing to make a widget in a dashboard t show the number of "complete, not started, or in progress" items I have. I know I need to change the status to a number. I have tried to do this with "IF logic but am failing. Can someone help with a formula for the following case?

If status is Not Started = 1

If status is In Progress = 2

If status is Completed = 3

Thank you!!!

Shane Klos

This only counts cells that are at a certain "Level" in the heirarchy and then it counts the "Health" for the given condition. In this case "red". You'll need a formula for each color. There are a million variations. Check out the courses around advanced formulas.

=COUNTIFS($Level$9:$Level$242, ="", $Health$9:$Health$242, "red")

Andrée Starå

Hi,

Not sure I follow!

Do you want to show a number, or do you want to count how many there are of each value?

Here's an example of the former.

Try something like this.

=IF(Status@row = "Not Started"; "1"; IF(Status@row = "In Progress"; "2"; IF(Status@row = "Completed"; "3")))

The same version but with the below changes for your and others convenience.

=IF(Status@row = "Not Started", "1", IF(Status@row = "In Progress", "2", IF(Status@row = "Completed", "3")))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

Just try this...

 

=COUNTIFS(Status:Status, "Not Started")

=COUNTIFS(Status:Status, "In Progress")

=COUNTIFS(Status:Status, "Completed")

.

These three formulas will show you "...the number of "complete, not started, or in progress" items..." you have.

nbannwart10

This is exactly what I needed. THANK YOU SO MUCH!!!!

nbannwart10

Thank you so much for this help! This did work as well, but I explained it wrong. The comment below is the formula I was looking for. Rookie mistake  

Paul Newcome

Happy to help! 

Andrée Starå

Happy to help!

No worries! The important thing is that you got a working solution!