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?
Ok. Give this a go:
=COUNTIFS([Column name]:[Column Name], @cell = "Posted") / COUNTIFS([Column Name]:[Column Name], AND(@cell <> "", @cell <> "Posted"))
Do you mean on a parent row, a sheet summary field, or in a separate sheet?
I have been using the top row as my totals column, but I can use the summary field if necessary
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 <> "")
That gave me an unparseable error message. I'm not great with multi-function formulas so I'm unsure how to troubleshoot.
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 <> "")
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"
I believe that formula did it. I truly appreciate your help. Thank you @Paul Newcome !!
Happy to help. 👍️
ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.
