COUNTIF with AND(OR not working

This should be easy and I've probably just been staring at it too long. I'm getting #Unparseable.

=COUNTIF([1 Weeks Tasks]@row = 1), AND(OR([Task Owner]@row = "CopyeditAMT@nwea.org", ([Task Owner]@row = "PadletAMT@nwea.org", ([Task Owner]@row = "LayoutAMT@nwea.org", ([Task Owner]@row= "AMT Distribution List")))))

This variation didn't work either. No #Unparseable error but it's not counting correctly.

=COUNTIF([1 Weeks Tasks]@row = 1, AND(OR(@cell = "CopyeditAMT@nwea.org", @cell = "PadletAMT@nwea.org", @cell = "LayoutAMT@nwea.org", @cell = "AMT Distribution List")))

Find more posts tagged with

Accepted answers

Jeana

Thanks for the input! Here's what I finally did that worked for me:

=COUNTIFS([Calc if Done]@row, =0, [End Date]@row, AND(@cell >= TODAY(), @cell <= TODAY(+7), AND(OR([Task Owner]@row = "CopyeditAMT@nwea.org", [Task Owner]@row = "PadletAMT@nwea.org", [Task Owner]@row = "LayoutAMT@nwea.org", [Task Owner]@row = "AMT Distribution List"))))

This checks to see if the task is NOT Done, AND within the next 7 days AND has a TASK OWNER of any of the lists after the OR.

Maybe this will help others with the same issue. :-)

Jeana

All comments

Thiago Castro

Hi Jeana

Let me know if I understood correctly. You want to

COUNT IF

Weeks Task = 1

AND

Task owner is EQUAL to

CopyeditAMT@nwea.org AND PadletAMT@nwea.org

OR COUNT IF

Weeks Task = 1

AND

Task owner is EQUAL to:

LayoutAMT@nwea.org AND AMT Distribution List

Is that correct?

Jenn Hilber

Try COUNTIFS instead: https://help.smartsheet.com/function/countifs?frame=0&nav=1, it allows more than one criteria

Include ranges rather than @row cell references

Add the numbers instead of using OR

=COUNTIFS([1 Weeks Tasks]:[1 Weeks Tasks], 1, [Task Owner]:[Task Owner], "CopyeditAMT@nwea.org") + COUNTIFS([1 Weeks Tasks]:[1 Weeks Tasks], 1, [Task Owner]:[Task Owner], "PadletAMT@nwea.org") + COUNTIFS([1 Weeks Tasks]:[1 Weeks Tasks], 1, [Task Owner]:[Task Owner], "LayoutAMT@nwea.org") + COUNTIFS([1 Weeks Tasks]:[1 Weeks Tasks], 1, [Task Owner]:[Task Owner], "AMT Distribution List")

Jeana

@Thiago Castro @Jenn Hilber

Hi, here is some clarification and better results after I took more time to think it through.

What I want to do is to COUNTIF 1 Weeks Task = 1 AND EITHER TASK OWNER AMT? OR ADDITIONAL RESOURCES AMT? = 1

The first condition that must be met is 1 Weeks Tasks = 1. Then IF Task Owner AMT? OR Additional Resource AMT? = 1 I want to the result to be 1 or True or Yes. Anything that tells me all conditions have been met.

This formula is working EXCEPT for the first and last row in this example. The results should be 0

=COUNTIFS([1 Weeks Tasks]@row = 1, AND(OR([Task Owner AMT?]@row = 1, [Additional Resource AMT?]@row = 1)))

Thoughts? Suggestions?

Screen Shot 2020-11-10 at 8.46.39 AM.png

Thiago Castro

Hi Jeana

I was going to suggest the same formula:

Unfortunately Smartsheet isn't smart enough yet to allow more than 1 condition within the same range.

A workaround that I usually find where I have 3 options and need to find 2 of them I use the "<>" (different) sign.

The second example you provided is also a workaround where you can easily count in the summary sheet and have the expected results.

Jeana

Thanks for the input! Here's what I finally did that worked for me:

This checks to see if the task is NOT Done, AND within the next 7 days AND has a TASK OWNER of any of the lists after the OR.

Maybe this will help others with the same issue. :-)

Jeana

Jenn Hilber

@Jeana,

For this example you have shown above, I think just having an =IF statement would work.

=IF(AND([1 Weeks Task]@row = 1, OR([Task Owner AMT?]@row = 1, [Additional Resource AMT?]@row = 1)), 1, 0)

Putting the AND statement first says that both [1 Weeks Task AND (either Task Owner AMP? -- OR -- Additional Resource AMT?)] = 1

Screen Shot 2020-11-12 at 10.17.51 PM.png

Does this work for you? I was unable to get the COUNTIFS statement to work at all in the above example.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.