COUNTIF with AND(OR not working

Jeana

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")))

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

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?

Thiago Castro

Hi Jeana

I was going to suggest the same formula:

=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")

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:

=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

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

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