Percent formula for drop down fields

Jenn_Sas

I have a column with the following options for drop down fields:

NA, Not Started, Required, Drafted, Posted

What formula can I use to show me % complete (posted) without taking into account the NAs?

Paul Newcome

Ok. Give this a go:

=COUNTIFS([Column name]:[Column Name], @cell = "Posted") / COUNTIFS([Column Name]:[Column Name], AND(@cell <> "", @cell <> "Posted"))

Paul Newcome

Do you mean on a parent row, a sheet summary field, or in a separate sheet?

Jenn_Sas

I have been using the top row as my totals column, but I can use the summary field if necessary

Paul Newcome

In a sheet summary field, you would use something along the lines of

=COUNTIFS([Column name]:[Column Name], OR(@cell = "Posted", @cell = "NA") / COUNTIFS([Column name]:[Column Name], @cell <> "")

Jenn_Sas

That gave me an unparseable error message. I'm not great with multi-function formulas so I'm unsure how to troubleshoot.

Paul Newcome

I also had a misplaced parenthesis. here is a fixed version to try:

=COUNTIFS([Column name]:[Column Name], OR(@cell = "Posted", @cell = "NA")) / COUNTIFS([Column Name]:[Column Name], @cell <> "")

Jenn_Sas

Thank you @Paul Newcome, that did give me a percentage. However, out of 78 rows, none say 'posted' so my % should be 0 but it's showing me 64%. Most rows show "NA" and some show "not started" and "drafted"

Paul Newcome

Ok. Give this a go:

=COUNTIFS([Column name]:[Column Name], @cell = "Posted") / COUNTIFS([Column Name]:[Column Name], AND(@cell <> "", @cell <> "Posted"))

Jenn_Sas

I believe that formula did it. I truly appreciate your help. Thank you @Paul Newcome !!

Paul Newcome

Happy to help. 👍️