Issue with CountIF/AND

matt_k · 07/21/18

Hi all,

I am running into an issue with using CountIF and AND. What I am trying to do is count the number of times certain text appears within a 90 day period (based on submission date which is a date column).

=COUNTIF(AND({External Range 1}, "Zebra", (TODAY() - {External Range 5} < 90)))

I am getting "Invalid argument set." I had understood that this issue is due to the fact that I am trying to work with a date column. I wanted to see what workarounds others may have tried for a similar issue.

Thanks,

Matt

L_123 · 07/23/18

try count(collect(

https://help.smartsheet.com/function/collect

countif does not allow for AND or OR.

Dave Godfrey · 07/23/18

If you are referencing another sheet have you tried adding a date range column and then use a countifs formula. I have a roll up sheet and have set a start date and end date, here is an example of one that works for me.

=COUNTIFS({Status}, "Won", {Date Quote Sent to Client}, >=$[Start Date]$3, {Date Quote Sent to Client}, <=$[End Date]$3)

Hope this helps

Dave

Paul Newcome · 07/23/18

It is not so much an issue of using a date, but an issue of your syntax and layout of the formula itself. You cannot use AND within a COUNTIF. You have to use COUNTIFS and break it down.

=COUNTIFS({External Range1}, "Zebra", {External Range 5}, < TODAY(90))

Another issue that may arise in other formulas is how you've built your AND statement. In COUNTIF or COUNTIFS the layout is

=COUNTIF(Range, Criteria) or =COUNTIFS(Range 1, Criteria 1, Range 2, Criteria 2)

In an AND statement, you use logical statements similar to an IF statement

AND(Range 1 > Criteria 1, Range 2 < Criteria 2, Range 3 = "Specific Text")

matt_k · 07/23/18

Thanks to all for their input. Paul's formula worked like a charm. Thanks Paul.

matt_k · 08/17/18

Hi all,

I am running into some trouble iterating on this formula for adding an "OR" to the first part. I want to see if the Range 1 contains "Zebra" or "Lion" and was entered in the last 90 days. After scanning these communities, I have tried this three different ways and keep getting invalid operation errors.

=COUNTIFS({External Range1}, "Zebra", OR (@cell = "Lion"), {External Range 5}, < TODAY(90))

I then also read a suggestion to create separate statements, but that has also produced the same error:

=COUNTIFS({External Range 1}, "Zebra", {External Range 5}, <TODAY(90)) + =COUNTIFS({External Range 1}, "Lion", {External Range 5}, <TODAY(90))

Thanks in advance for any help.

Paul Newcome · 08/20/18

=COUNTIFS({External Range 1}, "Zebra", {External Range 5}, <TODAY(90)) + COUNTIFS({External Range 1}, "Lion", {External Range 5}, <TODAY(90))

It was just the second =. When combining formulas, you only need the one in the very beginning.

Issue with CountIF/AND

Comments

Categories