Welcome!

Issue with CountIF/AND

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

Find more posts tagged with

Comments

L_123

try count(collect(

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

countif does not allow for AND or OR.

Dave Godfrey

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

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

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

matt_k

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

=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.

Sign in to join the conversation!

Quick Links

Trending discussions

Sharing with Editors outside our company
Good afternoon! My company works closely with another ambulance service, Company B. Both companies have a Smartsheet Enterprise plan. I have created a workspace that I need members of Company B to edit. Do I need to make them members on my account if they are already members on their account? Or do I just need to add their…
How to create stack column graph bars
I'm trying to create a graph chart that captures monthly count of records by divisions (Northeast, West and Central) that should be stacked with a legend displaying each by different color like a presented in Excel. When I attempt to duplicate the process in Smartsheet I receive the following results. In Excel, I created…
Can an admin of a sheet be from a different organization?
I have shared sheets and dashboards with people outside of my organization, and one has a paid license. Would I be able to grant admin access to this licensed user to their sheet? Or would the ownership of the space by one organization limit the admin accessibility of someone from outside?