Can Someone QA my Formula?

Meredith Rhodes

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

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