Can Someone QA my Formula?

I'm attempting to create a formula the matches the filter conditions that I have on this report:

I want to count the number of rows that meet these criteria (count if (this or this or this or this) AND (that or that))

=COUNTIFS(OR([All Results Expected]:[All Results Expected] <> "", [Corrections Expected]:[Corrections Expected] <> "", [Results Expected]:[Results Expected] <> "", [Update Expected]:[Update Expected] <> "") AND(OR([Record Status]:[Record Status] <> "No Longer Public", [Overall Status CT.gov]:[Overall Status CT.gov] <> "Withdrawn")))

This formula is #UNPARSEABLE.

Can you help me figure out how my translation is failing? (I tried the AI - and it didn't come up with the right answer)

Thank you, Smartsheet Braintrust!

Meredith

Find more posts tagged with

Formulas

Accepted answers

All comments

Meredith Rhodes

I guess I need to tell it what to count. Hmmmm.

Mark.poole

@Meredith Rhodes

I noticed one thing right away you forgot a coma between your first OR statement and your AND statement.

=COUNTIFS(OR([All Results Expected]:[All Results Expected] <> "", [Corrections Expected]:[Corrections Expected] <> "", [Results Expected]:[Results Expected] <> "", [Update Expected]:[Update Expected] <> ""), AND(OR([Record Status]:[Record Status] <> "No Longer Public", [Overall Status CT.gov]:[Overall Status CT.gov] <> "Withdrawn")))

From what I could see there is no other problems.

Meredith Rhodes

Thanks for you edit @Mark.poole, I've added the comma - this is still getting an UNPARSEABLE error.

I didn't give it directions to count a specific range. Is this what I'm missing? But if this also UNPARSEABLE.

=COUNTIFS([COUNT THIS RANGE]:[COUNT THIS RANGE], (OR([All Results Expected]:[All Results Expected] <> "", [Corrections Expected]:[Corrections Expected] <> "", [Results Expected]:[Results Expected] <> "", [Update Expected]:[Update Expected] <> ""), AND(OR([Record Status]:[Record Status] <> "No Longer Public", [Overall Status CT.gov]:[Overall Status CT.gov] <> "Withdrawn"))))

Mark.poole

https://community.smartsheet.com/discussion/comment/439625#Comment_439625

I have also researched it for you a bit. That is correct. OR functions don't really work with countifs. Unless your looking at the same range with different criteria. Instead it looks like you would want. I am still learning the Ins and Outs of the different formulas and what can be achieved with the OR and AND functions.

=Countifs([Count This Range]:[Count This Range],"your looking for",[All Results Expected]:[All Results Expected], <> "")+Countifs([Count This Range]:[Count This Range],"your looking for",[Corrections Expected]:[Corrections Expected], <> "")+….

Meredith Rhodes

Thanks for this idea @Mark.poole - I think that might double count rows where multiple criteria are met?

Mark.poole

It will indeed. I never said the work around was pretty, You would THEN have to go back and subtract from the total. I found this answer on Youtube of all places so I will not take any credit for it. Here is the link to the video I found.

https://www.youtube.com/watch?v=y-m1fbdmgU8

Meredith Rhodes

Thanks again @Mark.poole

Helper columns to the rescue. I made a column for each criteria on my sheet with a logic statement in each to return 1 if the cell was not blank. Then I added a fifth column to sum those - and a formula to count that row if it is equal to or greater than 1.

So much easier than attempting smartsheet countifs and or logic.

Thanks for the idea!
Meredith

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

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.